Try playing Amnesia the Dark Descent at night.

To the Moon.

The Binding of Isaac. There's not a lot of story at first, but when you unlock the endings, especially the later ones, you begin to understand a lot of what's behind the game. Might require the DLC Wrath of the Lamb for the final ones though.

L.A. Noire, planescape torment

Journey (PS3)

Angry Birds

Mass Effect 1+2
Fahrenheit (Indigo Prophecy)

Beware, there are soundtrack spoilers in this links, so don't click them if you want to enjoy the game as much as you can, these games made me cry, maybe I am oversensitive but nice music always makes me cry, that's why I am linking these games to the songs that made me cry (together with something what happened in the game or sometimes just the song..), I only browsed the games on my Steam that I have checked as Completed:

Bastion , Aquaria, Kane & Lynch 1, Blueberry Garden .... Dinner Date

Botanicula

I cannot emphasize how emotionally moving Heavy Rain is, smuggle it into your house and play it when everyone is asleep. It will make you forget you're playing a game.

Maybe Day of the tentacle?

Mafia II

Journey for the PS3!!!!

Sword and Sworcery, Spec Ops: The Line (it won't bring you to tears, but it made me feel some emotions).

LIMBO. Short but worth it.

Thomas Was Alone, Every Day The Same Dream, Don't Look Back - all free flash games, though Thommas is getting a full release soon. Aside from those there's Pathologic, but I never tried it myself. Supposedly, it's very depressing and it definitely uses at least one game mechanic that should be proof enough that it'll work, but I'm not spoilig it for you.

I would suggest Persona 3 on the PS2. I was really moved by the story, truly one of my top rated game stories ever.

American McGee's Alice and BioShock 2.

Bastion for sure!

Snuggletruck! It's so emotionally moving that you will probably want to beat a bear after playing it for an hour. XD

dear esther

1) Get a console
2) Play Lost Odyssey and Final Fantasy X.
3) Dive into the story and you'll shed tears.

BioShock 2.

Syberia, The Longest Journey, Bastion, Planescape : Torment, Dear Esther, Baldur's Gate,Limbo even Sid Meier's Alpha Centauri can be moving.

Did you play Call of Juarez, i found that story interesting. Its a shooter, so it not like Zelda, but you might have fun with it