September 10, 2016 at 8:07 pm
So here's the deal, I have a mirrored production database that I set up the transaction log file to a specific size allowing for normal work loads and maintenance when needed. Backups taken regularly to make sure space was reused when available. Then I ran into a problem with the index maintenance (short story is it wouldn't run right and grew the T-Log). At that point the log was big but not completely crazy but my maintenance wouldn't run right so I stopped running it and got some advice from someone more knowledgeable then me. His advice worked on fixing the index maintenance however now my T-Log is 65GB on a 100GB DB. Since the script he pointed me to worked, I believe I should be able to run index maintenance again more normally and want to gain back the T-Log space.
I saw a few other questions on this forum similar to my issue but not really answered as to what is the proper procedure for mirrored databases. Is it still recommended to change the recovery model to Simple? Do I need to stop the mirrored instance and do this procedure on the principal then restore a backup to the mirror? What gotchas aren't listed on MS's site about Shrinking a file?
Thanks
September 11, 2016 at 1:16 am
JoeS 3024 (9/10/2016)
Is it still recommended to change the recovery model to Simple?
It's never been recommended to switch to simple to shrink the log, and if you do that on a mirrored DB, you'll need to remove the DB from mirroring first, then set up from scratch
Do I need to stop the mirrored instance and do this procedure on the principal then restore a backup to the mirror?
You should just be able to shrink the primary. Log shrinks are logged operations and hence should be mirrored. Worst case, you'll have to fail over the mirroring and shrink the second DB as well, but I don't think that will be necessary.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 11, 2016 at 5:20 am
Thank you Gail for the clarification.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply