October 27, 2010 at 9:37 am
Scenario is for Sql Server 2005
1.full db backup is scheduled overnight (Mon-Sun)
2.Transaction log backup every 1 hour (Mon-Sun)
3. Shrink log every hour as logs really grow huge every 10 minutes
USE dbname
GO
DBCC SHRINKFILE (‘dbname_log’, 0, TRUNCATEONLY)
GO
No issues with performance.
My question is do we have to give ‘Checkpoint’ explicitly before shrinking the log files ? or do I have to add code from checkpoint before truncating log?
October 27, 2010 at 9:43 am
I would eliminate the step to shrink the logs. You'd be better off letting the logs grow to their optimal size. Shrinking the log reclaims the space, but your application is just going to grow the log again. Every time the log file (or data file for that matter) has to grow, you take a performance hit. Backing up the log will ensure that the log file does not grow indefinitely and fill up your disk.
I would stick with the log backups and see how large your log file grows. There should be a happy medium to where your log file is large enough for most of your normal processing.
October 27, 2010 at 9:44 am
That's well into the category of 'serious mismanagement of transaction logs'
Why shrink? They're just going to grow again. The fact that you don't see performance problems now is not a good reason for doing something that reduces performance.
If the logs are growing too big, increase the frequency of the log backups.
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
October 28, 2010 at 2:58 am
thanks guys.
I have disabled the shrik file job and increased frequency of log backups but i am still worried it might still fill up disk space.
October 28, 2010 at 3:06 am
you probably need more disk space. or put the log file on a drive having ample free space for the log file to accomodate its growth.
October 28, 2010 at 8:19 am
ps. (10/28/2010)
you probably need more disk space. or put the log file on a drive having ample free space for the log file to accomodate its growth.
I would say just keep an eye on it. The log file will not grow out of control if you are backing it up. If it was not filling up the disk before, why would it now? The shrink file step was unneccesary and was not keeping it from filling up the disk.
October 28, 2010 at 8:23 am
It may grow out of control where in there is sudden import of huge amount of data and some other scenario. OP needs to keep an eye on everything(better to swith to bulk logged in such cases) and resize his log file after doing capacity planning allowing enough room.
October 28, 2010 at 8:33 am
Right, OP needs to keep an eye on it. My point was that just because he/she is not shrinking the file does not mean that more disk space is needed or the file needs moved. The shrink file opertation is not what was keeping the log in check...it's the log backup. The idea of a sudden import that would fill the log would have had the same effect even if the shrink file was running after the backups.
Capacity planning for log file usage is a must. You need to know what your normal log file size is as well as what your highwater mark is during peak time or times where you have large imports or other operations that cause additional log file growth over and above normal processing. Then, of course, extra cushion space over and above the highwater mark is needed to ensure the log does not fill up completely. All of these things are a necessary part of administering the database and the OP needs to do this.
October 29, 2010 at 2:38 am
thanks a lot guys!
I agree with all you have said and will keep monitoring the transaction log growth.:-)
My next question is about Checkpoint.
Do I have to explicitly use checkpoint before/after taking transaction log backups?
Personally I don't think it's necessary but would like know your views on that.
Thanks,
October 29, 2010 at 2:48 am
you should leave checkpoint to sql server.
Checkpoint is called by sql server at certain frequency so that only that much dirty pages remain in memory which sql server can recover during startup within the time limit(defined by recovery interval configuration setting in sp_configure).
October 29, 2010 at 8:35 am
Agree with all the above.
You should look upon log shrinking as a last resort and even then you want to make sure that there is a valid reason. Its not just for daily maintenance.
If you need to shrink the log for space reasons, then you have an issue and that is issue is you need more disk.
If your backing the logs up every hour then every hour (however large thay are) they will become 99% free; so all you really need is enough space for the hours growth.
Ne careful.
Adam Zacks-------------------------------------------Be Nice, Or Leave
November 8, 2010 at 4:22 am
Thank you everyone..
Finally my isue is resolved and there is absolutely no issues with disk space and log backup...
Thanks again!
November 10, 2010 at 1:46 am
what if the initial log size is like 1 GB.
and for some reason log backup job failed
no-one noticed and the ldf file size went to 10 GB.
now even if we take log backups ldf size will not become 1GB instead it will remain 10 GB.
in this case we can shrink the log file after taking backup and make the log file size again to 1 GB(initial size)
Correct me if i am wrong.
Azad R Sale
November 11, 2010 at 8:07 am
That is a valid scenario where you'd consider shrinking the file. You want your log file to be sized appropriately to be able to handle your normal daily processing without having to grow (much). You still want to monitor your growth patterns so you can identify when something runs haywire and grows your log way past where it needs to be to handle your typical needs. This is when you could consider shrinking it back if you need to reclaim the disk space.
November 12, 2010 at 4:17 am
I agree with John.
I had to do it when one of my database was used for log shipping and later logshipping was disabled and forgot scheule log backup.. 1 Gb log was grown to 53 gig . To bring the log size back I had to take a log backup as db was in Full recovery model and then shrink the log file.My log file is 1 Gb now and becuse of frequent log backups log is not really growing big.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply