There are a few standout moments... Most recent one was my first unspoilered playthrough of Dragon Age Origins. I had made choices and got an ending that I simply was not expecting. It was in the wee hours of the morning, I had just loved the entire game... and I got so choked up at the end. The game rolled into the epilogue and I was teary eyed. I went to bed, woke up thinking about it and started a new game immediately, which I enjoyed just as much as the first time, made some different choices and got a different ending.

One of my other great gaming moments was in the original Witcher, at the end of Chapter 3, The Unforgiven, where you think you are fighting the big bad boss, or at least finally going murder his oily nasty little sidekick in the face. There is a fight in a tavern with some possible allies depending on a choice you made earlier, there is being thrust into some cavern to go after Azar javed, there are allies coming through portals, there is the big boss you have been after, there is a twist, there is you as Geralt chasing down the Professor, who is a complete jerk that you want to kill, there is a cavern, there is a GIANT BUG, there is a satisfying end to the Professor... and then there is a race where you are being chased by a GIANT BUG, collapsing mine supports, and trying not to get yourself killed.
The reason that moment sticks for me is that it took me 7 tries, because I was not paying attention originally to a huge hint the game gives you. I was so frustrated, and ready to quit, but I was loving the game. So I took a deep breath, reloaded... and finally went *Forehead slap* Collapse the mine supports!! It still took another try because of course at first I was so frazzled, I collapsed them on my head at one point. But then, when it all came together, and the Kikkimore Queen was dead, and I looted parts of the Professor from her (heh)... I was actually exhilarated. And then the game tosses you into Chapter Four, which was an area I loved.