Welcome to Android Central! Are you talking about Facebook Messenger, Android Messages, or the stock Messaging app on your phone? Which phone do you have?
Hmm, strange issue. I've been seeing threads about not getting notification tones on Pixels on Pie, rather than wanting to turn tones off but still getting them.
If you don't have any message threads you want to keep, try going to Settings>Apps, selecting Messages, then Storage, then Clear Cache/Data.