A replication fun fact!
ilkirk | Tuesday, January 20, 2009 | 0900I just wanted to share a little fun fact that we stumbled head-long into late last week, in the midst of a bumper crop of replication alarms.
SQL Server 2005 added the “Initialize from backup” option for Transactional Replication and only recently did we finally dive in and use it. We had encountered a situation where it was the entire database being replicated (one table…!) and we didn’t want to schedule a snapshot to get things back in line. Instead, we decided to leverage this “new” technology we’d been sitting on for more than a year.
I won’t get into all the details, because so many others have already, but I will point out that this is an option you set at the publication level. Once the option is turned on, the distribution database mantains all of the replication commands, based on the LSN, from the last backup. (I haven’t tested to see if this is a full backup or transaction log backup - we were operating in simple mode).
This becomes a bit of an issue when you’re inserting and/or deleting several million records and your distribution database is sitting on one of your smaller mount points. Despite running the distribution cleanup command over and over, with an ever shrinking max retention number, our ms_replcommands table grew wildly out of our control as more and more transaction were pumped in. Eventually we stemmed to flow of transactions by stopping the log reader agent while we researched the problem.
Basically the distributor was waiting for the next subscriber to come along, saying I used X backup and I need to catch up - can you tell me what I’ve missed? Since we weren’t going to have another subscriber come along, we really should have flipped that switch to the off position after we’d finished the initialization process - its a completely online change. As soon as we turned off that option, the distribution clean up ran and swept all but the most recent changes out the door.
That’s not one they seem to mention, so I figured I’d let it rattle around in the Google-bin.
For reference, here is the Microsoft Books Online link:
