Final Fantasy 6 -
The first game that really made me cry was Final Fantasy 6 on the Snes. I remember just after the floating continent, and the world was destroyed, waking up on Solitary Island. The sad music, the desolate blowing wind and feeling like you were the only person left in the world. It filled me with melancholy and a gave me a feeling of complete hopelessness.
Panzer Dragoon Saga -
One of the most mature and well crafted stories of the era and to this day is just as refreshing. The way all of the characters have their own ambition and motivation for what they are doing made them human, not just a cluster of polygons. Major battles are fought over misunderstandings and you get drawn into religious politics because you ride a dragon in the sky. The world and its people are alive. Nothing however, could prepare me for the end. As the end of the game approaches none of the characters are really sure whats going to happen to them. The dramatic uncertainty plays out across several cut scenes that made me tear up and filled me with anxiety. Most games you get to the end and know what to expect more or less. In Panzer Dragoon Saga it's mostly uncertainty. The characters don't really know whats going to happen. The player doesn't really know and it just made my stomach knot. Then after the credits another cut scene throws a bit of a twist and another heaping pile of anxiety.
Enemy Zero -
For pretty much the same reasons celerystalker pointed out. With limited saves and loads, it makes you feel the weight of each action you take. Once you start encountering the invisible enemies and the different tones and beeps it gets insanely tense. It still gets my heat pounding when there are multiple enemies. Any other game if you die you just reload and its not a huge deal. Not here though, limited saves and loads. VERY tense game.
If a god is willing to prevent evil, but not able, then he is not omnipotent. If he is able, but not willing, then he must be malevolent. If he is both willing and able, then why is there evil? If he is neither able or willing then why call him a god?
Only game that has ever made me sad to the point where I was thinking about it all night after, is the final boss battle in mother 3. Won't spoil it for anyone but yeah
Marble Madness inspired rage.
I'll have to go with earthbound. I have yet to finish it, I was a bit young when it first came out (born in 1991). I watched my brother play a whole bunch of it, and recently bought the 3DS port to I can finally play through it myself.
The whole home sickness bit, and leaving your mom. From what i've seen it really was touching, exciting, sad, and funny all at once.
As far as new games, Life is Strange really hit me pretty hard.
I thought it was going to be some silly coming of age game, or your basic "girl goes to school, we see her grow".... but this was so much more than that. Without spoilers, it was a true emotional roller-coaster. I teared up really bad a few times and it truly surprised me. It's one of my favorite games now, and I can't recommend it enough.
A couple SNES games were the earliest, but there were also PSX games.
Breath of Fire with a late game scene with characters named Alan and Cerl. The music helped add to it because it was a really sad sounding song.
The end of Lufia 2, also the beginning of Lufia. I've played Lufia 2 long before I played the original and at that time, I made my way through the game along with these characters. If I was to play through Lufia 1, I don't think it wouldn't have made a difference because you spend a whole 10 minutes with the characters before they die.
As for PSX, the love story with CLaude and Rena on Star Ocean the Second Story was probably the thing that most got me out of all of them.
I actually didn't play the original XCOM until a few years ago before playing the remake. But being inside an alien base and seeing my first Crysalid really invoked a feeling of feer, especially as long of a distance it traveled and ohkod one of my allies, only for me to kill it, and then the next turn my ally exploded into another Crysalid from the zombie that was created. Obviously the allies that were around that Crysalid died and I was pretty scared about the safety of the other members in the base since I knew the ones that got kiled would turn into additional Crysalids.