Nah, when I got to the level where I was on the surface, going through the old buildings, I thought the game was going to take the Stalker approach but eventually the pace builds up afterwards. Creepy that the fact that you would be alone in dark rooms in certain missions? Yes. Scary? No.
It's not scary because you always know that something bad will happen, that some sort of monster will appear. The problem with the last Horror games that came out, espacialy Horror FPSs, it's that they don't have the "element of surprise" anymore. I would be more scared by a game with no monsters in it, but which gives you the impression that you're being watched in every moment, than a game full of monsters, but in which you know that in the next room is a bunch of trolls, and you can kill them with your bazooka.
i was avoiding metro 2033 for a similar reason. I'm such a chicken. Same reason why I avoided the STALKER series for so long. But then I played and fell in love with STALKER... but I still wouldn't play it at certain times of day :P
It is not scary, it CAN be eerie and stressful (the latter people often mistake for scary)
It is never really scary, but skulking outside in the hars frozen corpse of the city, picking supplies of frozen corpses, while constantly watching for the somewhat agressive wildlife IS somewhat tense, watching the air supply, listening to the uneven breathing, and watching the frost on the visor is not exactly calming, but scary it is not.
Part of the reason why Metro is not (and can not) be scary is due to the fact that you have a gun, you kill things, ANYTHING really. Not much to be afraid of when you can simply blow it away you know?
But it IS mystical, eerie and somewhat desolate from time to time. There is One section on an abandoned railway tunnel that really branches into the supernatural, but even that is far more sad then it is scary.