Why SQL Server Replication depends on Database Mirroring

Had a convoluted replication problem this morning that turned out to be caused by mirroring.  Users noticed a lack of data on a replicated server that was quickly traced back to replication failing to deliver commands. The Log Reader Agent noted the following:

Replicated transactions are waiting for next Log backup or for mirroring partner to catch up

There was nothing wrong with log backups, so mirroring was the next thing to be checked.  It was suspended, and attempting to resume it failed.  Checking the Mirroring Monitor yielded nothing helpful, but the SQL Server error log on the host server showed the following:



Date x/xx/xxxx x:xx:xx AM
Log SQL Server (Current - x/xx/xxxx x:xx:00 AM)

Source spid34s

Message
'TCP://foo.bar.com:5022', the remote mirroring partner for database 'SomeDB', encountered error 3624, status 1, severity 20. Database mirroring has been suspended.  Resolve the error on the remote server and resume mirroring, or remove mirroring and re-establish the mirror server instance.

On the mirrored server, there were additional errors, including a stack dump, but this was the most relevant:



Date x/xx/xxxx x:xx:xx AM
Log SQL Server (Current - x/xx/xxxx x:xx:00 AM)

Source spid39s

Message
Error: 3624, Severity: 20, State: 1.

---

Date x/xx/xxxx x:xx:xx AM
Log SQL Server (Current - x/xx/xxxx x:xx:00 AM)

Source spid39s

Message
SQL Server Assertion: File: , line=807 Failed Assertion = 'result == LCK_OK'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.

Severity 20 == bad.  This basically comes down to corruption on the mirror target, which irreparably broke mirroring.  This, in turn, broke replication.  

So why, the technical user asked, does replication between business-critical Production servers depend on mirroring to a disaster recovery environment?  I had to think about that one, but there's a good reason.

Assume replication does not depend on mirroring.  Server A is both the replication publisher and mirror source.  It replicates rows to Server B and mirrors to Server C.  Mirroring breaks at time t0.  Rows continue to replicate, and are marked as replicated on Server A.

Server A fails over to Server C at time t1.  Replication picks up from there with Server C as published.  Server C republishes the rows between t0 and t1 to Server B because it does not have a record of those rows having been replicated.

Makes sense now, right?

Shrinking log files of formerly replicated DBs

To start, I should note that a) there's probably a better way to do this, and b) I'm sure this is already out on the net somewhere, possibly even on my old blog as a post from Mike Forman. But this way works, it's fast, and I couldn't Google it when I needed it, so I'm posting it again.

In our QA Static and Dev environments, we have static copies of a DB that is a replication subscriber in Production. This DB gets refreshed (restored) from Production every few weeks, or when I break everything and have to restore a fresh copy. When restored, it still has transactions marked as pending replication in the log, and so the log cannot be shrunk.

Naturally, we don't have as much space as we'd like in QA and Dev, and since the DB in question is static, it doesn't need the 50 GB transaction log that it has in Production. So I wanted to shrink the transaction log, but was prevented from doing so by this error:

Cannot shrink log file 4 (Test_Log1) because all logical log files are in use.

So how do you fix this?

My solution was to fool (read: hack) the system tables to make SQL think this DB was still a replication subscriber, then use sp_repldone to set all the transactions to done. This short script will do it, although I recommend you BE CAREFUL, because this is the master database you're messing with.

sp_configure 'allow updates', 1
reconfigure with override

update master.dbo.sysdatabases set category = 1 --
where dbid = 10 -- replace with appropriate dbid for your DB

use Test
GO

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

update master.dbo.sysdatabases set category = 0
where dbid = 10 -- ditto

sp_configure 'allow updates', 0
reconfigure with override

After the sp_repldone, you'll be able to shrink the log file to your heart's content. There may be another way to do this, such as attaching the DB without its log file, but I'm not sure that works, and besides, I like hacking system tables.