Database Mirroring - Subscriber Recovery

Question

Hey guys,

 

I just had a quick question regarding the database recovery of a mirrored SQL 2008 SP2 instance.   What exactly happens during the recovery of a mirrored database?  

The situation I'm interested in is let's assume that we have a high traffic database that is being mirrored and there was a low change transaction that had begun, but was never committed and because of this the principal instance's log file had blown up to 400GB.  Normally, when restarting the SQL server, it would have to traverse the entire log to figure out what needed to be rolled back/forward and this would take several hours before the database would be fully online.

As far as the subscriber goes, would it need to do that the same thing or would the subscribe realize that only that one transaction needed to be rolled back and would ignore the rest of the log thus making recovery of the subscriber take minutes instead of hours.   

 

Thanks

 

Answer 1

The recovery process only only needs to recover trans that are not yet recovered regardless the size of the log which can be large due to various reasons.

 

Check out this links:

http://technet.microsoft.com/en-us/library/ms191154.aspx

http://msdn.microsoft.com/en-us/library/ms188748.aspx

 

 

---

-- Yaniv www.sqlserverutilities.com http://blogs.microsoft.co.il/blogs/yaniv_etrogi

Answer 2

Ahh ok, so recovery should be much faster since it won't have to re-read the log and just rollback the uncommitted ones then.

 

Thanks

Answer 3

Yes, in order to bring the db to an on line sate it has to be first recovered in which sql srv rolls back or forward (commits) any open tran.

The recovery time is impacted by the recovery time interval as better explained in the links.

 

---

-- Yaniv www.sqlserverutilities.com http://blogs.microsoft.co.il/blogs/yaniv_etrogi

Answer 4

Also, FWIW, be sure that you have run a log backup on the principal.  This will also limit how much of the log file it looks at for mirroring.  A log backup will not cause any loss of changes that may still need to be mirrored.

RLF

Answer 5

Yeah, we found out the hard way that we needed to do log backups on the principal.  Usually we use log shipping as a DR strategy and we take the automatic log backups of it for granted.  

We plan on doing more testing with it, but the initial failover of a live "test" system took about 20 minutes until we decided to bounce the server.  We were looking at DB monitoring metrics for it on both the principal and the mirror, but we weren't able to nail down what the bottleneck was.

By the time we had done the failover, we had shrunk the log enough (it about at about 3GB) but it still took a long time.  Any ideas off the top of your heads?

The reason for all these question is that we're looking to add another layer of availability above Windows clustering and the DB we're mirroring is about 1.4TB at this point so we want to nail down all the kinks in DB mirroring.

Thanks guys.

Answer 6

Did you have a really big transaction running (such as reindexing a table) that could have caused lots of update traffic?   When we run DB maintenance jobs, it can delay our mirror catching up for 20 minutes or so.

FWIW,
RLF

Answer 7

Hi Russell,

The transaction that happened was during an upgrade of SP2 from SP1 CU5 where the upgrade failed during replication upgrade scripts causing a transaction to be left by a system spid for 30+ hours.  We have alerting for long running spids, but it ignores system spids unfortunately.   This caused the log to blow up to 400+ GB leaving us in a precarious situation where restarting the server caused our main production DB to be down for 4+ hours while a rollback was happening.

As for normal traffic, our main DB is highly OLTP via jobs and just user traffic, so we'll be looking for ways to reduce batch sizes to ensure quick rollbacks if necessary.  

Thanks for the heads up on the maintenance jobs.  We usually reorg high traffic tables nightly so we'll have to watch out for that.