I personally use the startup sound for Big Picture mode for message notifications; it's nice and smooth so I don't jump about a mile when it's 1am and I get a message while half asleep.
BP sound file: Steam\tenfoot\resource\sounds\activation.wav
Make a copy of it and name it message and stick it in Steam\friends and you're good to go
Edit: fuck i forgot steam broke sound modding, see post #16 to use my workaround