From what I've read as to the origins of Sweet Caroline, Amy Tobey, who's assignment was to decide what music would be played at Fenway from 1998 to 2004 heard it at other ballparks, liked it, and started using it late in the game if she felt the Red Sox were going to win, whether they were ahead or behind. The fans loved it, and the new ownership eventually decided to play it at every home game.
I've also read, though, that it was played as a tribute to someone who worked at Fenway having a baby and naming it Caroline.
Whatever the reason, I love it. It's been my ringtone on my phone for some time now!