I ironmanned MGS4 (yes, I'm crazy like that) and fell (feel?) asleep during one of the cutscenes at the end. Couldn't have been very long since it was still going when I woke up.
A friend of mine fell asleep while battling the Orb boss in Baldur's Gate: Dark Alliance (PS2). I noticed because his character kept running into a wall and wasn't helping me. I turned around and sure enough he was snoring right next to me (ah, the good old couch co-op days).
I recently nodded off for a bit while waiting turns in XCOM:EU (it was like 3am and I had been playing for 20hrs straight).
Also, I was ironmanning Batman: Arkham Asylum and I think I may have dozed off while in the Batcave as well.
It usually happens when I'm playing super late or after a very long session. It's rarely out of boredom (I'll just quit the game if that's the case).