SQL Server 2008 R2 Mirror Database - Shrink Transcation Log
-
Monday, April 04, 2011 3:02 PM
How can I shrink the transcation log of a mirrored SQL Server 2008 R2 Database that is in a Mirror, the transcation log has grown too big and is consuming all of the diskspace on a drive?
All Replies
-
Monday, April 04, 2011 4:32 PMModeratorYou can't as the database is in restore only, read-only mode. The only way to do that is to run a DBCC SHRINKFILE command on the primary database and the command will be committed on the mirror database. This, of course is not recommended. If your issue is disk space, I suggest you move your database files on the mirror server to a higher capacity disk. This is not pretty straight forward as you may need to re-initialize your mirroring session
Edwin Sarmiento SQL Server MVP
Blog | Twitter | LinkedIn- Proposed As Answer by Russell FieldsMVP Monday, April 04, 2011 5:28 PM
- Unproposed As Answer by Allen.Feinberg Monday, April 04, 2011 9:48 PM
-
Monday, April 04, 2011 9:48 PMSo if it's not straight foward is there any guidance on how to to this....You say that running DBCC SHRINKFILE is not recommended....so what is recommended...we have a 800 GIG transcation log we need to shrink.
-
Monday, April 04, 2011 10:30 PMModerator You may have to do this off-peak hours. Shrinking a database is not recommended but, in this case, you don't have much choice. Try to avoid shrinking database files is possible1) Run a LOG backup on the primary database. This will truncate the log2) Run a DBCC SHRINKFILE to shrink the log file. Try using a 50 GB intervals until you have managed to shrink the log file to as small a size as possible. For example, DBCC SHRINKFILE (2,768000) -- This will shrink file number 2, assuming it is the log file, to 750 GB. Then, repeat the process, providing a 700GB size, etc.If a 50 GB interval is taking long, lower down the interval to 25 GB. Remember that this will be committed to the mirror database so expect a much longer time than a database that is not configured for mirroring3) Once you have shrunk the log file to the smallest size possible, resize it back to, let's say, 8 GB. This depends on the amount of transactions you have for this database. The goal is to make sure that you have enough space in the log file to avoid it from growing repeatedly. This also minimizes the number of virtual log files4) Once the log file is shrunk to a smaller size on the principal, check the log file size on the mirror to verify that the changes are applied.5) Make sure that you have regular LOG backups to truncate the log on a regular basis, freeing up space and preventing it from growing
Edwin Sarmiento SQL Server MVP
Blog | Twitter | LinkedIn- Proposed As Answer by Ashish Kumar Mehta Wednesday, April 06, 2011 9:47 AM
-
Thursday, April 07, 2011 8:05 PM
It is possible you would need to do log back up twice. So, if after the first backup you cant shrink the log file, back it up again and then shrink.
MC
-
Saturday, April 09, 2011 9:14 PMAnswerer
We have developed a script (stored proc) that helps us to shrink the log file in principal . You may use this idea too
* Declare the final size that you want for your log file
* perform log Backup
* Checkpoint
* Shrink the log file and check if the size has reached the final one (that you have declared)
* Repeat and go through the same steps until the file has shrunk to expected size.
But definitely I wouldn't suggest you to run this during the production hours or peak transaction times . I have had to use this only a couple of times in my environment in 3 years where log file drive was running out of space and business cannot afford new disks.
Thanks, Leks -
Sunday, April 10, 2011 11:33 AM
I would suggest you to configure regular log backups, only transactional backups will flag the inactive portion of log space to be reused ie your log file will not grow. Please configure more frequent backups that would resolve this issue.
http://uk.linkedin.com/in/ramjaddu -
Sunday, April 10, 2011 11:42 AM
Hi
>>I would suggest you to configure regular log backups, only >>transactional backups will flag the inactive portion of log >>space to be reused ie your log file will not grow.
If he performs mirroring, that means he does backup log files on principal...
BTW you need to allocate plenty of space to your lofile in order to prevent frequent growing
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/ -
Sunday, April 10, 2011 12:08 PM
Performing Mirroring that does not mean he configured log backups.
Mirroring operates on transactional level - in synchronous mode transaction commited both partners same time where as asynchorous transactions commit without waiting for the mirror to write the log to disk.
A database mirroring session runs with either synchronous or asynchronous operation. Under asynchronous operation, the transactions commit without waiting for the mirror server to write the log to disk, which maximizes performance. Under synchronous operation, a transaction is committed on both partners, but at the cost of increased transaction latency.
There are two mirroring operating modes. One of them, high-safety mode supports synchronous operation. Under high-safety mode, when a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible. As soon as the databases are synchronized, a transaction is committed on both partners, at the cost of increased transaction latency.
The second operating mode, high-performance mode, runs asynchronously. The mirror server tries to keep up with the log records sent by the principal server. The mirror database might lag somewhat behind the principal database. However, typically, the gap between the databases is small. However, the gap can become significant if the principal server is under a heavy work load or the system of the mirror server is overloaded.
In high-performance mode, as soon as the principal server sends a log record to the mirror server, the principal server sends a confirmation to the client. It does not wait for an acknowledgement from the mirror server. This means that transactions commit without waiting for the mirror server to write the log to disk. Such asynchronous operation enables the principal server to run with minimum transaction latency, at the potential risk of some data loss.
http://uk.linkedin.com/in/ramjaddu -
Sunday, April 10, 2011 12:32 PM
>>>Performing Mirroring that does not mean he configured log >>>backups.
Are you sure?
The following are the pre-requisites for database mirroring.
- Edition of SQL Server should be Standard, Enterprise or Developer edition
- Principal Database involved in database mirroring should be in full recovery mode
- Before configuring database mirroring, take a full backup, transactional log backup on the principal server and restored it on the mirrored server with NORECOVERY option.
Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/ -
Sunday, April 10, 2011 1:02 PMYes I am sure - that is only for initial configuration which needs to restore full back and atleast one transactional backup with norecovery mode to configure synchronous /asynchronous mode.
http://uk.linkedin.com/in/ramjaddu -
Wednesday, May 23, 2012 4:09 AM
configure backup plan of principle server
full backup every day
diff backup twice/thrice a day
suggested log backup every 15- 30 mins for your environment .
it worked for similar size mirrored environment...hope this helps
-
- Proposed As Answer by sqldbarocks Wednesday, May 23, 2012 4:09 AM
-
Friday, March 08, 2013 7:24 PM
Remove the Mirroring
change the db recovery model to simple
and shring the long file
it will free up the space .
then reinitiate the mirroring again
Thanks
Please Mark As Answer if it is helpful. \\Aim To Inspire Rather to Teach A.Shah
- Proposed As Answer by Logicinisde Friday, March 08, 2013 7:24 PM
- Edited by Logicinisde Friday, March 08, 2013 7:25 PM
.png)
