Obvious answers that I will agree to (SPOILERS AHEAD, and you should know that by now if you're in this thread; but I'll try to keep it somewhat general):
FFVII, death of Aeris. This was the first game where the characters felt real to me, and this scene evoked tears from me more than once. It was so unique for its time, which is probably why we all still remember it. I also remember feeling sad about the deaths of Jessie, Biggs, and Wedge near the beginning of the game.
FFX, horribly bittersweet ending. I was really rooting for Tidus by game's end, and his fate is very unfulfilling.
Shadow of the Colossus, the bit with Agro and later the ending where the game gives you futile control. I just played this game and that really tore me up, even though the ending in retrospect was probably not that sad.
Silent Hill 3, the death of Harry Mason, beloved father and general good guy.
I'll second these lesser-suggested items:
Dead Space ending, the fate of Isaac's girlfriend is revealed. Though this one didn't stick with me as well as some others, probably because (as Yahtzee pointed out) Isaac's kind of a robot.
Backstory recordings in BioShock. Also, the Frank Fontaine thing left me completely gobsmacked. Ryan's final scene was pretty sad as well when you fully understand the plot, even if he was kind of an evil dictator.
Grand Theft Auto IV, the death of a character close to Nico (depending on the choice you made). I got the girl killed, which I thought was bad, but finding out the result of the other option (which is probably more true to the storyline) was a lot worse.
My own additions:
Kingdom Hearts 2, where Roxas learns he's basically not real. I just spent several hours playing as this guy, and now his friends don't even remember he existed. Really sad for me.
Persona 4, when a beloved character flatlines in the hospital. This really upset me and made me wonder if I'd done something wrong.
Eternal Darkness: Sanity's Requiem. I don't even remember the character's name; he's the second one you control, the page to Charlemagne. He's desperately trying to bring this sword to his boss, but the evil (or whatever, it's been a while) slowly possesses him while you're controlling him. This was well before the CoD4 character dying of radiation poisoning; there's something affecting about a character you control decaying before your eyes. Anyway, you fight and "kill" this character later as somebody else - he's completely zombified by now and uses the sword against you, whispering "Charrrlemaaagne..." Powerful stuff.
In (Forbidden) Siren (very scary and enjoyable plot despite some gameplay hiccups), you control many characters at different times, and several of them (including some very likable ones) wind up dead by the end of the story. Possibly the saddest is the girl in the track suit: She evades capture in her final mission and makes it to the church where her parents are, only to become a shibito (zombie) and scare the bejeezus out of them instead of being safe (and later turn them as well).
Earthbound, the general story arc of Pokey/Porky made me sad, especially the way he appears in Ness's dreamworld. Actually, pretty much everything in the dreamworld had a very sad tone for me; I think the makers captured the feeling of a quixotic and unfulfilling dream very well, and I've had a few of those.
Silent Hill, Cybil's death struck a chord with me (since it's apparently canonical). Harry's only real friend and the town kills her - or worse, sort of makes Harry do it. Also, the "death" of Lisa was a tough one to watch.
There are more, but I'm tired. (-: I love it when video games have an emotional impact on me.