March 28, 2012 at 9:13 am
I'm reading a lot about the "evils" of log file shrinking. It shouldn't really be done but sometimes it's a "necessary evil" to be used to take care of things.
With that being said I'm going into this armed with a bit of information.
What I'm looking at trying to do is once every xx amount of hours, have a script run that will go out, loop through the log files & check the percentages. If it's at a certain percentage, automatically shrink the log file and then send me an email that the shrinkfile just took place. Or at the very least, email me that the log file has reached a certain percentage & needs to be shrunk.
Has anyone heard of this being done? If so, does anyone know where I can find some code to do something like this?
Thanks!
March 28, 2012 at 9:20 am
Unless you are experinece space issues on the disks, or you have experienced an unusual event in a database (large data import or delete), I would not shrink the t-logs. They have grown to a specific size for a reason, if you shrink them they will just have to grow again which can impact system performance.
For more information please read the last article I reference below in my signature block on Managing Transaction Logs.
March 28, 2012 at 5:50 pm
John Waclawski (3/28/2012)
I'm reading a lot about the "evils" of log file shrinking. It shouldn't really be done but sometimes it's a "necessary evil" to be used to take care of things.With that being said I'm going into this armed with a bit of information.
What I'm looking at trying to do is once every xx amount of hours, have a script run that will go out, loop through the log files & check the percentages. If it's at a certain percentage, automatically shrink the log file and then send me an email that the shrinkfile just took place. Or at the very least, email me that the log file has reached a certain percentage & needs to be shrunk.
Has anyone heard of this being done? If so, does anyone know where I can find some code to do something like this?
Thanks!
Yes. On a single server, it can actually be done pretty easily. I have to ask, though. What do you mean by "percentage"? % free space... % compared to MDF file size? or ???
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2012 at 5:52 pm
Jeff Moden (3/28/2012)
John Waclawski (3/28/2012)
I'm reading a lot about the "evils" of log file shrinking. It shouldn't really be done but sometimes it's a "necessary evil" to be used to take care of things.With that being said I'm going into this armed with a bit of information.
What I'm looking at trying to do is once every xx amount of hours, have a script run that will go out, loop through the log files & check the percentages. If it's at a certain percentage, automatically shrink the log file and then send me an email that the shrinkfile just took place. Or at the very least, email me that the log file has reached a certain percentage & needs to be shrunk.
Has anyone heard of this being done? If so, does anyone know where I can find some code to do something like this?
Thanks!
Yes. On a single server, it can actually be done pretty easily. I have to ask, though. What do you mean by "percentage"? % free space... % compared to MDF file size? or ???
In addition to the % Free space - is that relative to internal the log file or to remaining space on the disk system.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 29, 2012 at 6:33 am
I recently posted about implimenting a zipcode to city state decoder and ip 2 lookup decoder. Well when i did i ended up createing the ip table which had almost 4 million records and the zip table who had i think around 800,000 records. As a result my log file grew rather substancially.
It wasn't as simple as just shrinking the log file. You've go to do a backup so the db can do a checkpoint and allow u to free up the space. Backup then shrink. I suppose the shrink would work after the next scheduled backup but whatever. Just figured i'd mention it. Again, this really only applies if the log file has grown very large after the last backup.
March 29, 2012 at 7:46 am
Since you really want to shoot yourself in the foot at certain moments , why not just add another factor of uncertainty by using the sqlserver alerts.
Have a look at http://www.simple-talk.com/sql/backup-and-recovery/alert-based-transaction-log-backups---automate-your-database-maintenance-part-2/
This article launches a backup job, but it can easily be modified to perform the shrink.
IMMHO it would be better to investigate the log file explosions ( your default trace contains info regarding who and when files have grown ) and only then perform a shrink if needed
OR
even better size your log files correctly and take care of VLF fragmentation in a decent way.
refs:
Monitoring SQL Server Virtual Log File Fragmentation
Still goes for SQL 2008 !!!
***************************
Performance impact: a large number of virtual log files – Part I ( Linchi Shea )
Performance impact: a large number of virtual log files – Part II ( Linchi Shea )
or Kimberly L. Tripp’s blog post at http://www.sqlskills.com/BLOGS/KIMBERLY/post/8-Steps-to-better-Transaction-Log-throughput.aspx.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply