It's too hard to pick one so here's my top 5:
1. The last level of Hitman:Codename 47. I had a handgun with a few bullets left and after the last level I felt like I could take on anything. Then I saw that i had to kill 10 clones of Codename 47(your character) all armed with machineguns. I just said to myself in disbelief "I am so f****d" and then died a horrible death.
2. The death of Sniper Wolf in Metal Gear Solid. It was just like a movie only most movies aren't as good as this.
3. When Cloud kills Sephiroth at the end of Final Fantasy 7. The way Cloud's Limit Break filled just by looking at Sephiroth and then he lets rip with Omnislash was the perfect way to finish him off.
4. The first time I blew a zombie's head off in Resident Evil. Ahh memories.
5. The intro movie to Final Fantasy 8. Just beautiful.