This is mostly about RPGs but if you can find a non-rpg entry feel free to speak up.

Many games usually end with your character battling against and inevitable doomsday scenario against a villainous or monstrous figure that would do Cthulu proud.

So what games gave you the best sense of "you can't win" by creating a climatic scenario that seemed too great for your party or character to overcome? The Game where you actually really started to wonder how things would turn out and maybe thought this wasn't going to end with a Happily Ever After.