Everyone seems to complain about MGS2's ending, but I loved it! It was lengthy and satisfying, if a bit odd at times. The last boss fight was rather cool as well...
Anyways, there are few games that really can provide truly memorable endings...but the type I enjoy usually provide excellent last boss encounters combined with a fairly lengthy and emotional ending. The two Klonoa games (especially the original) are great examples of this. The boss fights were solid and fun to play, but the endings were SO incredibly sad that it would be hard to forget them anytime soon.
On the RPG front, Xenosaga certainly provided a beefy ending, even if it did leave the door open. It lasted, what, 30 minutes or so? I really enjoyed Final Fantasy X's ending as well. Not only was it fairly sad, it also had some truly beautiful music (perhaps the best song in the entire game).
What else? Hmmm...
I really dug the ending to ICO. They managed to accomplish so much with virtually no dialog. The ending sequence itself was powered solely by the incredible imagery and music. What certainly made it even more special was the entire portion of the game leading UP to the end (from when you wake up in the rain until the last fight). The series of events and challenges (minor challenges) were EXTREMELY well done and left a great impression upon completion. The last boss of ICO wasn't particularly difficult, but it was an incredible encounter and was quite unique.
Another favorite of mine is the ending of Sonic Adventure 2. I loved the game already, but I certainly recognized its faults (the searching levels). However, the entire ending sequence not only presented each of the characters in a gameplay setting that they should have originally been a part of (Knuckles has a good stage?!?)...it also provided a great last boss encounter (with awesome music IMO) and a pretty good ending. Up until that last act, the story felt rather segmented and akward...but that final hour long section (or so) really brought everything together beautifully.
I see that Mafia was mentioned here, and I agree. The game had one of the best plot lines I have ever encountered in a game and had an absolutely AMAZING ending. God I loved that game!
Shenmue 2 is another one. The entire last disc was basically one big ending and it absolutely kicked ass! After the quick pace of disc 3, it was quite a change and really left quite an impact (unlike Shenmue 1's ending).
Breakdown had a fairly slick ending, I thought. As did Shadow of Destiny (well, it had many endings with lots of cool revelations that eventually came together to form a final ending). I'm sure there are plenty of others...but off the top of my head, that's about all I can think of.
There are plenty of disappointing finales, though...almost too many to list. I'll just name a few...
The original Splinter Cell had one of the worst endings I have seen in a LONG time. The last mission was quite cool (love that "ninja" outfit), but the finale was completely worthless and uninteresting. I did not finish Pandora Tomorrow for that reason (that and the fact that the game was way too linear).
Far Cry also springs to mind. I certainly can't say I loved the game, but some of the later missions in the game were fairly cool (from Boat onward). However, the final area was flat out AWFUL. I hated every fight along the way and the actual ending was completely worthless and stupid. I wish I had simply stopped playing before the Volcano level, as I might have been able to walk away with a better impression of the game...
Primal was disappointing. It wasn't an awful ending, but it didn't grab me like the rest of the storyline did. Primal had some of the best characters I've encountered in a game, and the story was good enough that it compelled me to finish the game (despite many gameplay flaws). However, the ending fights just weren't exciting (like the rest of the combat) and the final cutscenes didn't really close up the game very well. In fact, the last cutscenes I remember well are when Scree finally revealed who he was...which was awesome. However, I really can't recall the ending (outside of being disappointed by it).
System Shock 2 is one of my favorite PC games of all time, but the ending left me disappointed the first time BECAUSE IT DIDN'T WORK!!! The codecs for playing movies in the dark engine are very fragile under WinXP...and the ending cutscenes and credits did not play. The tribute to Shock 1 was AWESOME, of course...but it is quite a blow when you finish the boss and the game kicks you to the main menu. So, the ending wasn't really bad...but my initial experience with it was.