The most tense moment in a game

[Almost Famous]

Any FPS when you are comfortable in a sniping spot then realize you're surrounded and being shot at from all directions.

 

[Beffudled Sheep]

The last 2 hours of gameplay of MGS4 and then the last 2 hours of cinematics of MGS4.

This. Crawling through that microwave chamber and the fight with Liquid was tense.
That and the RE remake. Middle of the game, shotgun shells, 10 pistol rounds, and in the middle of a dark forest.

 

[resultsmayvary]

resident evil 4 mercenaries mode when your going for gold star

This.

In the mercenaries mode for the little village from the beginning of the game, when you're trying to get yourself positioned well, and you hear the chainsaw in the distance. Always tensed me up some.

 

[Xhumed]

First time playing We Don't Go To Ravenholm, running out of ammo and with no handy lethal objects nearby, and hearing the roar of a Black Head Crab Zombie...

Also, playing the finale of Blood Harvest on L4D for the first time, and realising there are no molotovs or pipe bombs lying around...

Speaking of L4D, the other day I was playing Dead Air on single player (after getting frustrated with my slow connection trying to play Versus). After lowering the crate to progress, having survived all the waves of Infected, I climbed up the ladder... to come face to face with a Tank, without any warning sting of music. Right at the top of the ladder.
Needless to say, I back-pedalled very rapidly.