Alan Wake for sure, and one of my all time favourites, Turok for N64! Dinosaurs running out of the fog at you was so incredible back then.

YES!

3 Days centered around Clocktown. Reset and do it over from different perspectives before a killer moons destroys everything.  Truly genius.

I didn't want to mention every game, but this would be one of my top picks too. Everything was so grim and there was a constant sense of foreboding in the game. Easily my favorite Zelda game.

Bioshock comes to mind. The atmosphere in Fragile Dreams was great as well.

Dead Space 1 & 2
Half Life 2 (Especially Ravenholm & Episode 1)
Halo
Arkham Asylum (Scarecrow segments were executed perfectly)
Majora's Mask
Limbo
Alan Wake

One game came to mind and soared higher than ANY other game ive played.

No not Uncharted 3 XD.

^SO SCARY XD

Shadow of the Colossus for those that didn't get the picture.

Portal: You have this odd, empty feeling about the place, and it gets better the more you go.
Chrono Trigger: Whether you are on a desolate, empty future, on a cold age, you always have this connection with the world.

Every Valve games.

(HL2, Portal, L4D)

Alan wake for me . and bioshock,

Let's see here... I'll keep it to games from this generation:

The Legend of Zelda: Skyward Sword/Twilight Princess (Brilliant art direction and level design tend to do that.)
The Elder Scrolls V: Skyrim (It's biggest strength is how engrossing the land of Skyrim is. You can spend days getting lost in the land.)
Batman: Arkham Asylum/Arkham City (Rocksteady did such a fantastic job of getting the spirit of Gotham City down in both games.)
Portal (Like Metroid Prime, the game does a fantastic job of making you feel like you're all alone... but Glados and the past test subjects make Aperture Science feel alive.)
Cave Story (Brilliant pixel art and wonderful music really make Cave Story's world come alive.)
Castlevania: Lords of Shadow (Great use of Catholic/Christitan and Pagan mythology really make this game it's own. Really nothing else out there like it.)
Disney's Epic Mickey (Everything in this game is somehow rooted in Disney's lengthy and storied history... and it comes together brilliantly.)
Bioshock/2 (One of the most atmospheric games I've ever played. Everything in this game plays its part to make Rapture feel like a once-living city gone to Hell)
Super Mario Galaxy/2 (Pitch perfect controls, camera, and level design suck you in. And the wonderful music and diversity of action keep you wanting more.)
Endless Ocean/Blue World (Gorgeous graphics make this underwater wonderland come to life... and the gameplay is so relaxing... truly a unique experience.)
Muramasa: The Demon Blade (Some of the most gorgeous visuals in a video game I've ever seen... and the haunting music seals the deal.)
Metroid Prime 3: Corruption (Atmosphere is one thing that the Prime series has always excelled at, and Corruption is no exception.)
Okami (This game is pretty good Monty, you should try it.)