I really liked the bit in SR3 where you're just driving around in a car with Pierce while singing together (badly). Games often struggle to convey the idea of friendship between characters, and this little scene managed that perfectly.
For an actual gameplay thing, killing Ragnaros for the first time in vanilla WoW would certainly qualify, way back in September 2005 if I remember right. By the end of that fight my hands were shaking so much that I could be mistaken for a Parkinson's sufferer. What made it sweeter is managing to pull off a ninja jumper-cable resurrection on a healer by vanishing and switching trinkets midfight.
Other memorable moments:
- Being exploded into pieces by Beren in Ultima 8. As a sheltered kid, I'd never seen that level of violence in a videogame before. The feeling when I managed to exact my revenge by recreating this method of drowning him filled me with warm fuzzies. (To this day I'm still pretty uncomfortable with all that blood and gore stuff. A few years later I was similarly shocked by Fallout's default level of violence, and proceeded to turn it down to the minimum setting.)
- Wing Commander 4 FMV. Technically my first FMV game was probably Return to Zork (bundled with a Sound Blaster), and I suppose Rebel Assault sort of had it (same), but I never had a PC able to run WC3 until after the next game was released. Seeing the opening cutscene for the first time was an amazing experience. "Multimedia" PC had been a buzzword for some time before that, but this for me was what finally delivered on the concept. (Shame about the missiles, after a while I ended up just turning invulnerability on and finishing the game that way)
- Winding back even further, I remember struggling to play SimFarm on a 286. It chugged, and the graphics were significantly compromised on an EGA graphics card. Then my dad got a DX2 486 laptop (supposedly a work laptop), and I was amazed at the difference it made running the game. Sure, the laptop's trackball is awful, and the screen was a DSTN monochrome panel, but never mind that, it was amazing all the same. There was a time in my life where SimFarm was my favourite game of all time, so it was a big deal in context.