Moonlighting was cancelled because they totally effed up David and Matie coming together; what killed it was the one-night-stand and the pregnancy and Maddie-marrying-someone else for no good reason. Mulder & Scully were never really shown to have got together till the series finale - what killed it was the lack of Mulder and Scully in the later seasons and the fact that it had been running for nine years.
I got into fandom via Lois and Clark, and I can tell you this with authority - that show's ratings were at an all-time highest in Season 3, when they finally got together. What drove everyone away were the wedding-cop-out plot twists, frog-eating-clones, amnesiac-Lois-Lane and the manipulative psychiatrists who love her, long-lost relatives from outer space, psychotic wedding crashers and all-round godawful writing in the fourth season. Their coming together was actually the main draw for fans.
Yes, 12 years later I'm still bitter. What of it? (OMG, has it really been twelve years? Shit)