Replication is not my favorite, it is kind of far from my favorite. No further than that. Little further.

When it breaks, it can cause havoc and it always seems to break at the worst time. Recently we noticed that our logfile was massive (like 3 times the size of the database) and that was making many of the other processes painful. We didn’t know how long the log hadn’t been clearing so we got to burn it all (kind of).

The first thing I did was tell replication that we were done with all the transactions that had been committed.

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,@time = 0, @reset = 1

checkpoint

Had I known when replication had broken, I could have narrowed it down and put more details in this command to only clear out exactly what I needed to remove. Since I didn’t know, I told it to just be done with all of it. I ran a checkpoint trying to get it to clear out. At this point I took a log backup (I just kicked off my agent job).

“Burn, Replication, Burn!”

Then I checked to see how my log was looking and wanted to see if there were files that could be used.

DBCC LOGINFO('MyDatabaseName')

I was looking for status 0 (meaning the logfile could be reused or overwritten). There were still a lot that hadn’t cleared out so I repeated the process a few times and finally it was awesome! I had to do some clean up on my transaction log, but it was soooo much more usable.

We also had to re-initialize replication, but it was totally worth it.

The song for this post is Burn Butcher Burn by Joseph Trapanese and Joey Batey