Memorable campaigns
I have several memorable campaigns in mind. None of them are D&D although the current one is and I hope it will be memorable (we're closing our second year soon...)
Star Wars
The campaign lasted one year. It involved the characters finding the old ambandoned Earth (called, surprisingly, Terra Solaris III) on which a mythical artefact (in fact, the black stone from Mecca) could help enhance the Force and thus help Luke in training new Jedi to defeat the Empire. They did it, and when they came back, the Empire had been defeated anyway.
The campaign was memorable mainly because PC interaction was great (to be honest, the plot and NPCs weren't that great) and especially so after one session that went absolutely great, all the players ended at five in the morning hyper tense, so intense it had been... Sadly, the campaign is also memorable because one of the players died in a car accident that year, and we finished without him...
Vampire
The vampire campaign lasted one year also. It involved the characters siding with the Camarilla during the French Revolution when the Sabbat stirred trouble and disposed of the Prince of Paris. They were given a top priority mission of fleeing Paris with an invaluable artefact, protect it from anyone and give it over to the Prince of a city in Southern Europe. They had to battle hordes of revolutionary peasants, assamites and their own urges. One of the characters ended the campaign mute after a terrible rage when he killed many innocents. The player decided that his character could not speak after that. It was "that" intense.
The next part of the campaign involved the same characters in Saint Petersburg just before the Russian Revolution. There they had to understand who was pulling the strings behind a web of intrigue. It turned out to be an ancient Baali who fled accross Europe with the characters on his toes. The campaign went through Turky during the kurd massacres and thought the vampire was responsible when in fact it was only human folly. Finally they ended in Egypt and chased the vampire to his ancient tomb. He died there at their hands, but it was clear to them that he had used them as the instruments of his death rather than been killed fair and square.
That campaign worked because of interesting NPCs, and a great collaborative spirit between characters who had all reasons to NOT work together. It also worked because Vampire was a very different game from all that had been done before...
Fading Suns
The FS campaign lasted a year. The characters were involved in a space wide trek concerning an old forgotten space station from the second republic. They finally found the station, and unwittingly released an ancient demon who had been trapped there in a mystico-technological artefact.
It was great because of character interaction, cunning enemies, character mysteries (one character started as an amnesiac...) and experimental roleplaying that actually worked.
The current campaign is a potentially long lasting one. It's full of more classical elements that I won't divulge in case my players are lurking.
I believe though, that one vital element to a memorable campaign is for it to have a beginning and an ending. Same as a great TV series, really...