July 3, 2009 at 2:28 am
Hi Everybody,
I am having a logfile and its size is nearly 100GB, but i want to shrink the file. I know how to shrink it manually. My doubt is Can't we shrink the file by using the Maintenence Plan. I need to shrink the log file when its size crosses 100GB.How to make this? Can it possible?
Thanks in Advance,
Venu Gopal.K
Software Engineer
INDIA
July 3, 2009 at 2:50 am
You generally don't want to shrink the log file, if it's growing to 100GB regularly after being shrunk, then you want to make more space available for it. Every time the log file grows it will create fragments on the storage device, fragmentation is bad!
In an ideal world we would want to set the log file to a maximum size and hope that it never grows.
If you really want to do this, you can create a stored procedure that checks sys.sysfiles system table for when the file exceeds your threshold then issues the shrink commands. Be aware that the size column is in pages if I remember correctly, so you will have to do something like (size * 8)/1024 to get MB.
Akeel
July 3, 2009 at 2:51 am
My first question when I hear about a 100Gb logfile is what is the recovery model of the database? If it is not Simple, when do you take regular transaction log backups? If you do, what's the frequency of these log backups.
under normal circumstances it's very unlikely for a transaction log getting this big.
You can use a shrinkfile operation as part of a maintenance plan, but it's considered a bad practice.
[font="Verdana"]Markus Bohse[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply