I do things manually. I think of sounds I want, and I go find them. If you can find/learn your way around a sound editor to save just the portion you want (I use Cool Edit Pro / Adobe Audition) and encode it as, say, a 64kbps mono MP3, you are golden. Why be limited to what an app offers you?
I have, however, saved a few choice sounds from a couple of soundboard apps. Search for "Soundboard" in the market, and there's tons of free ones. If you find some you like, most of these apps offer a "save as ringtone/notification" option. Example: get the Looney Tunes one, and you can have Bugs Bunny saying "What's this? A letter, for me!". Heh.. perfect email notification.