Hmm. The following titles have all provoked what I would call "a strong emotional response" due to elements of great pacing and character or mood plus some memorable moments or levels:
1 - Ico
2 - Shadow of the Colossus
3 - Flower
4 - Cloud
5 - Silent Hill 2
6 - System Shock 1 & 2
7 - Uncharted 2
8 - Valkyria Chronicles
9 - Portal (awesome, awesome use of mood and tone and pacing and cake)
10 - Half Life 2 plus Episodes
11 - Vampire Masquerade : Bloodlines
12 - Siren Blood Curse
13 - Call of Cthulhu Dark Corners of the Earth
14 - Amnesia Dark Descent
15 - Thief 3
16 - Halo CE (the good version on PC - I keed I keed)
Some more recent examples without going back to the days of stuff like Westwood's excellent Blade Runner title.
I like some of the moments/cutscenes in stuff like Final Fantasy / MGS etc but I find I just can't quite connect with their more erratic tone and nature and approach. RPGs in general I always seem to find a mismatch between gameplay style and narrative. It's hard to put my finger on why as they're often loaded with 'big moments' in cutscenes, but they never quite work for me.