March 9, 2011 at 8:49 am
The server team will be taking down one of our db servers for maintenance for approx 4 hrs. This server houses 3 mirrored databases for our prod environment. With this outage being rather long, I'm not sure what I should do about the primary databases. 2 DBs are high performance and the other is high safety mode.
For the high performance ones, if I pause mirroring it should not effect the primary db's except I have to worry about the log filling up still right?
For the high safety one, if I pause mirroring it WILL effect the primary db and I will also have to worry about the log filling up right?
Is it best just to break the mirror? That just seems like a lot of work for scheduled maintenance.
I'm curious to find out what other people do. Your input is greatly appreciated!
March 9, 2011 at 3:05 pm
I think it really depends on how large your transaction logs are now, and whether or not you have enough room in them to handle the increased usage.
For example, I have a 1.2TB database that is mirrored in high performance. The transaction log on that database is 100GB which is needed for weekly maintenance (index rebuilds, batch updates, etc...).
As long as the outage on the mirror does not coincide with my maintenance window - I would have several days of transaction log space that can be used before I see any problems.
Also, you can remove mirroring - and as long as you have the log chain from the time you broke the mirror up to current time, you can just apply those transaction log backups to the mirror and enable it. You don't have to perform a full restore to enable mirroring again.
Of course, this also depends on how big the databases are - how long it will take to copy the backup files, restore the databases, etc...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 10, 2011 at 6:54 am
Thank you for your response. Since this will occur after hours I believe the DBs in high performance mode should be fine - I'll just watch the log usage closely.
For the DB in high safety mode - will this halt all db activity since it cannot proceed because transactions cannot commit on the mirror?
Also, you can remove mirroring - and as long as you have the log chain from the time you broke the mirror up to current time, you can just apply those transaction log backups to the mirror and enable it.
If there is a db backup in the middle of the log chain, can I still just restore the logs?
Thanks for your help!
March 10, 2011 at 8:23 pm
SQLdba-473999 (3/10/2011)
Thank you for your response. Since this will occur after hours I believe the DBs in high performance mode should be fine - I'll just watch the log usage closely.For the DB in high safety mode - will this halt all db activity since it cannot proceed because transactions cannot commit on the mirror?
Also, you can remove mirroring - and as long as you have the log chain from the time you broke the mirror up to current time, you can just apply those transaction log backups to the mirror and enable it.
If there is a db backup in the middle of the log chain, can I still just restore the logs?
Thanks for your help!
I am not sure about the high safety mode - I think what happens is that mirroring ends up disconnected and transactions are queue. However, I would not take the chance and would suspend mirroring or remove it manually.
And yes, as long as you have a unbroken log chain you can apply the transaction logs.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply