I seem to recall running into a bad checkpoint when the helicopter shows up just before you rappel into the nuclear missile base in COD4. I believe I was dicking around looking for intel when it showed up. Since I had played through it numerous times over the last few years I wasn't too worried at first, but I got stuck trying to cross the level back to the rappel point when the chopper showed up, and it took me probably ten tries before I managed to cross without getting mowed down. I believe it also started me off half dead already each time. Luckily I've played it so many times that I saw it as more of a challenge than an annoyance or I would have just said screw it.
But I think the worst checkpoint I ever ended up with wasn't really a checkpoint per se. I was playing PoP: The Sands of time and was in the middle of the fight right before the boss battle. It's been a few years since it happened so I'm somewhat sketchy on the details, but I believe I finished the fight and saved as I had to go somewhere. When I played the game next, the save file started off right at the final battle, skipping over at least one entire cutscene where the girl died and I presume, the Prince went back in time to stop it from ever happening. I'm a little sketchy on the details like I said, probably thanks in part to the game skipping over a fairly important part of the story on me.