PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Course Of The Month
9 days, 15 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Shrink tlog while mirroring a database
Posted on 2009-04-14
Hello all,
I am trying to find information on the best way to shrink a transaction log while the database is being mirrored. Typically I set the recovery to simple and then run dbcc shrinkfile to bring the tlog down. With mirroring this is not possible as the mirror must be in Full recovery mode. My question then is two fold.
1.) Can a transaction log be shrunk while mirroring? Since the db is in full recovery mode and I run dbcc shrinkfile I usually get an error message unless I set recovery to simple.
2.) If I do set recovery to simple while mirroring what happens to the mirror and if it breaks is there a quick method for reestablishing the mirror?
I appreiciate anyone's help.
Keith
I am trying to find information on the best way to shrink a transaction log while the database is being mirrored. Typically I set the recovery to simple and then run dbcc shrinkfile to bring the tlog down. With mirroring this is not possible as the mirror must be in Full recovery mode. My question then is two fold.
1.) Can a transaction log be shrunk while mirroring? Since the db is in full recovery mode and I run dbcc shrinkfile I usually get an error message unless I set recovery to simple.
2.) If I do set recovery to simple while mirroring what happens to the mirror and if it breaks is there a quick method for reestablishing the mirror?
I appreiciate anyone's help.
Keith
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4-
3
2
9 Comments
1. sure
2. No
remember, truncating the log is different than shrinking the file. If you're doing regular log backups (hopefully you are) then you can just use dbcc shrinkfile to shirnk the log file.
2. No
remember, truncating the log is different than shrinking the file. If you're doing regular log backups (hopefully you are) then you can just use dbcc shrinkfile to shirnk the log file.
The only way to clear up space inside a transaction log (also known as truncating) if the database is in the Full Recovery Model (as is required by mirroring) is to back it up.
So back it up regularly. Like... every few minutes. You don't need to keep the file if you're not interested in it (and clearly you're not if you're used to setting the database to Simple). If you do this, the transaction log shouldn't grow much, and you shouldn't need to shrink the log file at all (which ultimately just makes it have to grow more often - and growing log files is a painful operation).
Honestly - you should be trying to avoid having to shrink log files. I think it's only very exceptional circumstances that should make you want to shrink a log file.
Rob
So back it up regularly. Like... every few minutes. You don't need to keep the file if you're not interested in it (and clearly you're not if you're used to setting the database to Simple). If you do this, the transaction log shouldn't grow much, and you shouldn't need to shrink the log file at all (which ultimately just makes it have to grow more often - and growing log files is a painful operation).
Honestly - you should be trying to avoid having to shrink log files. I think it's only very exceptional circumstances that should make you want to shrink a log file.
Rob
Rob,
I understand your point about backups. During the day we do run log backups. The issue that I have is on weekends we also run a reorginize on the database to keep the database efficient and also as compact as possible. It is during this maintenance task that the log file will fill up to 120GB. I also run tlog backups during the maintenance reorginize but drive space has become an issue. This is the reason I will run dbcc shrinkfile.
In a circumstance such as this what would you recommend?
I understand your point about backups. During the day we do run log backups. The issue that I have is on weekends we also run a reorginize on the database to keep the database efficient and also as compact as possible. It is during this maintenance task that the log file will fill up to 120GB. I also run tlog backups during the maintenance reorginize but drive space has become an issue. This is the reason I will run dbcc shrinkfile.
In a circumstance such as this what would you recommend?
Since you can't switch to Bulk Logged (because you're doing mirroring), you're better off doing what you're thinking about doing a shrink after a log backup.
You should remember that if you're mirroring, then that 120GB of log will have to be sent to the mirror as well.
Depending on how large your database is, you may prefer to stop mirroring, switch to Simple, do your reorg, then switch back to Full and re-establish your mirroring.
Or else ask yourself why you really need to do a reorg? Are you checking for index fragmentation before rebuilding them, for example?
Rob
Depending on how large your database is, you may prefer to stop mirroring, switch to Simple, do your reorg, then switch back to Full and re-establish your mirroring.
Or else ask yourself why you really need to do a reorg? Are you checking for index fragmentation before rebuilding them, for example?
Rob
Rob,
I appreciate your suggestions. I am going to look into scripting out a job for switching back from mirroring, Simple mode, reorg, Full and back to the mirror.
I have to ask you one more question based on your last question. Should I check for index fragmentation and if so based on what statistical value should I kick off a reorg?
Thanks. The points are yours.
Keith
I appreciate your suggestions. I am going to look into scripting out a job for switching back from mirroring, Simple mode, reorg, Full and back to the mirror.
I have to ask you one more question based on your last question. Should I check for index fragmentation and if so based on what statistical value should I kick off a reorg?
Thanks. The points are yours.
Keith
Keith,
It's all going to depend on how you feel your performance is being hit. If you're largely doing Seeks on your index, then you will barely notice fragmentation, because each Seek will still take about 3 reads.
If you're scanning, then you'll be following quite a few more links than you would ordinarily need, and this is where the pain will come in.
Unfortunately, it's like the length of string. Some people feel fragmentation pain early, some people can have massive amounts of fragmentation and really not care.
Definitely check the level of fragmentation before reorganising an index. It's not worth the pain if you don't need it. But also make sure you've baselined your performance, so that you can work out what pain you're experiencing (before and after). See what happens (preferably on a test machine) if you ignore the fragmentation a bit longer, etc... but definitely monitor what the fragmentation is like, to try to get a feel for what you should do.
Also bear in mind that tables will have different uses. Some will bear much higher levels of fragmentation, while others will hurt you quite quickly. The better you understand these different levels, the better time you'll have managing it.
Rob
It's all going to depend on how you feel your performance is being hit. If you're largely doing Seeks on your index, then you will barely notice fragmentation, because each Seek will still take about 3 reads.
If you're scanning, then you'll be following quite a few more links than you would ordinarily need, and this is where the pain will come in.
Unfortunately, it's like the length of string. Some people feel fragmentation pain early, some people can have massive amounts of fragmentation and really not care.
Definitely check the level of fragmentation before reorganising an index. It's not worth the pain if you don't need it. But also make sure you've baselined your performance, so that you can work out what pain you're experiencing (before and after). See what happens (preferably on a test machine) if you ignore the fragmentation a bit longer, etc... but definitely monitor what the fragmentation is like, to try to get a feel for what you should do.
Also bear in mind that tables will have different uses. Some will bear much higher levels of fragmentation, while others will hurt you quite quickly. The better you understand these different levels, the better time you'll have managing it.
Rob
Rob, I appreciate your time in making your response. It is people like you that help a young dba like myself make progress. Thanks.
Featured Post
Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month9 days, 15 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
623 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.