While not exactly a single level, I really enjoyed the train sequence in Uncharted 2. It was what, 3 chapters worth? All I know is I loved the crap out of it. Makes sense I suppose since Naughty Dog put so much time into it.
Mass Effect 2 and the suicide mission. Everything was tense, decisions had to be made, and coming out alive with everybody? Man, I felt like just kicking back and opening a soda, saying "Yup, feels damn good..."
And in Mass Effect 3, priority Earth. I know some people dislike it for the constant waves of enemies that come at you, but I remember thinking to myself: "Well ****, glad I got the hell out of dodge when I did". How many enemies did I destroy just to get 5 (or was it 10?)blocks to the damn rally point? May not have been easy getting as many allies as I could, but I was sure glad I got away from Earth in the beginning. Just the amount of devastation and enemies really made it seem like an invasion. Oh, and the final talk with your crew...too many emotions.
Oh, and I guess the final level in Heavy Rain. Was so nervous trying desperately not to screw up.
I'm positive there is a lot more that I'm forgetting. There are a bunch of games I enjoyed a lot but can't exactly find any area that stood out to me specifically (like Dead Space 2). I know there has been a lot though.