There's quite a few to list so I'll stick to the most emotional:
Mass Effect trilogy (especially 2 & 3)- The character development in these games was just superb. In the first two you really develop attachments to the characters to the point where there's an immensely depressing (in a semi-good way) feeling of dread by the final game. You genuinely want these characters to survive.
Half-Life 2 (especially episode 2)- The cast of characters surrounding Gordon Freeman are so well fleshed out down to the smallest nuances that when events do befall them, you can't help but feel for them.
Metal Gear Solid 3 & 4- The endings, that's all I'm going to say.
Final Fantasy VII- As the first full-blown RPG I played back when I was only 14 years old, the entire story just sucked me in and the death of a main playable character came as a complete shock.
The Walking Dead game- Every episode had its moments and the desire to protect Clementine just seemed to grow with each one.
Shadow of the Colossus- This just needs to be played and experienced. There's not really anything else like it.