With Only Days Until It Starts, Now is the Time to Check it Out
We have pulled together more than 35 all-technical sessions from the top names in the world, all for you. Remember when you saw virtual conferences and thought "hey, this could be really great!" – but then got tired of sales webinars?
You won’t find any of that here.
It’s all tips, tricks and how-to information. Tips learned by doing. Tips learned that you can apply immediately. We literally guarantee it. But you have to register… and now is the time – it all gets underway in just days.
>> Get more information about the Fall 2012 SSWUG.ORG Virtual Conference here – and register ASAP, time is running out!
Should I Shrink My Transaction Log
Today Price writes in with a topic for consideration; Shrinking Your database. He writes,
I enjoy reading your emails every day. Here’s a possible topic that generates a lot of controversy because most SQLServer people have a firm opinion.
The topic is AutoShrink. And when AutoShrink is turned on AutoGrow is usually turned on too. Whenever AutoShrink is brought up in a session in the conferences I attend nearly everyone but me says it is really, really bad. They say it sucks up cpu cycles, clogs log files with too many VLF’s, takes time from SQLServer that s/b used to handle data, takes time for the disk to expand or shrink, causes disk fragmentation, and so on. But these guys cannot provide a link or some kind of data where these theories have been tested and data show it being detrimental. So far in my research on the internet all I have seen are some opinions – no data to prove it.
To me their chastising me for using AutoShrink is like being criticized by telling me that if I don’t use Pennzoil in my car my gas mileage will go down 10%. So far I’ve thought “Yeah sure”. But if there are data to support their views I would like to see it. Also if there are data to refute their views, I would sure like to see that.
We use SQL2005, SQL2008, and SQL2008R2. We use AutoShrink and AutoGrow on our DB’s, setting log files to a minimum size of about what we think will hold 50-60% of the transactions created between our log backups which range from every two hours to every Sunday, depending on how the DB is being used. Our SQLServers perform very well and our statistics rarely show CPU usage above 60%. The disks seem to work fine AFAICT. Response time for users is excellent. The DBO (me) does not have to continuously check the DB’s to make sure there is enough disk allocated. Based on our results, I don’t believe the risk of a data or log file running out of space is worth the minimal cost of AutoShrink and AutoGrow. Plus, I don’t think it is prudent to have a big file that is 5% full most of the time.
And to make my case even further, our SQL servers are up five nines. No SQL production DB has gone down because it ran out of space over the past couple years. To my memory we have done only one log file restore on production DB’s and that was three years ago.
What do you think?
Well, since he asked what I think I have the following comments
- You don’t have to look very long to find discussions about the bad results you may experience from shrinking a database. Many have a great deal of technical illustration as to how it does not work well
- The example provided here was for transaction logs. Transaction logs work very different from data files. I can see how they would actually benefit from shrinking in a different way than data files. However, setting an appropriate CheckPoint timing for your Simple Recovery databases, or doing Transaction Log backups if you wish to keep your Bulk or Fully logged database transaction log files for growing too large, can keep you from having to shrink your transaction log files, because they won’t grow as large to start with..
- Personally, I believe if you needed space once, you are going to need it again…so leave the space there; especially for data files
- Growing and shrinking files result in disk fragmentation. SAN drives may cover up the performance of fragmented drives; but, direct attached storage performance may degrade if this happens too much.
- Personally, I leave AutoGrow turned on for emergencies. I turn off AutoShrink and manage that manually. When I say emergency for AutoGrow, I truly mean emergency. A database tends to follow a pattern of growth. So, if it is growing from an AutoGrow request, it may well be under stress. Better to add space during lower traffic times
- If you wish to keep from fragmenting your disk, you can create more than one data file for your database. In other words, if you extend an existing database file it may be fragmented with other files in between. If you create another file in the same file group, it starts out fresh.
- Disk space is cheap. I only shrink if backups are getting too large. Even then, compression handles empty space rather nicely
Well, what do you think? Those are my preferences. Is there a difference in a Transaction Log file where it make sense to shrink it? Can it be better managed with backups?
Write in with your thoughts or suggestions to btaylor@sswug.org.
Cheers,
Ben
Featured Article(s)
DML Triggers to Track SQL Server Agent Jobs Creation, Deletion and Modification Events (Part 1)
This article shows the steps, which you can use to implement a monitoring solution with the help of DML triggers to track SQL Server Agent Jobs creation, modification and deletion activities in real-time.
Featured White Paper(s)
Key Management in the Multi-Platform Environment
Written by Townsend Security
This White Paper discusses the challenges … (read more)