Why shouldn’t I shrink my database log file?
10May 15, 2017 by Kenneth Fisher
TL;DR: It’s pretty pointless and can cause performance issues.
Let’s start by asking why you might want to shrink your log.
It’s too big
I find that people who say this frequently don’t have a firm idea of what is too big or even why it might be as big as it is. This also goes in with the I need to free up disk space with no good reason why the space needs to be freed up.
There are good reasons to shrink the log and they do revolve around space. For example:
- I had a one-time explosive growth of the log due to a large data load.
- The usage of the database has changed and we aren’t using as much of the log as we used to.
- We are billed at 2 am based on space used. We will grow the log back again after the billing period.
- I need to clean up a large number of VLFs. (of course, then you are going to grow it back manually right?)
It helps performance
I’ve heard people say performance but that’s always been one of those magical “I had a problem and this fixed it” or “I’ve been told it helps” type things without any proof behind them. I honestly can’t think of any way a smaller log is going to help performance. (It can help with restore time but let’s hope that’s unusual and it probably isn’t worth the cost.)
So let’s think this through a bit. What happens when we shrink the log?
It get’s smaller! Duh!
Well yes. But then what happens when your database sees it’s regular level of activity?
Size: Unless you shrunk it because it wasn’t actually using all of the space it had (see the first two reasons above) then the log is going to grow right back to its previous size isn’t it? Which makes it rather pointless right?
Performance: If you are shrinking your log then you are probably relying on auto-grow when the log runs out of space. So that growth is going to be taking place in the middle of one or more transactions. What you may not realize is that log growth is a slow operation. Even if you have instant file initialization turned on additional space added to a log file has to be zeroed out (0’s written to every bit in the space). So as your operations require the extra log space they are going to have to stop and wait for the growth operation hurting your performance.
Also depending on your growth size, you are going to mess with the number of VLFs in your log file. I’m not going to write much on that here other than to point you to Kimberly Tripp’s (b/t) great post on the subject: Transaction Log VLFs – too many or too few?.
Now if you are actually having a problem with log space and want the to know how to reduce the amount of log space you use, so that you can then shrink the log and have it stick I have some advice here.
SQL Studies 
[…] room to take that huge log backup or a full one for that matter. You need to clear out the log, get it shrunk and move on (with regularly scheduled log backups […]
[…] I told you not to shrink databases, even though I do it when I need to. […]
[…] while back I did a post about why you shouldn’t shrink your data file. This one is going to be similar in some ways, different in […]
..another fear-based DBA post.
“What you may not realize is that log growth is a slow operation. Even if you have instant file initialization turned on additional space added to a log file has to be zeroed out (0’s written to every bit in the space). So as your operations require the extra log space they are going to have to stop and wait for the growth operation hurting your performance. ”
Performance doesn’t matter at 2am when nobody is using it. Take ALL the time you want, as long as it’s not sending 45GB of data offsite when it only needs 400MB. Furthermore, it USED to be a slow operation on mechanical drives…a mechanical needle has to physically move and write those zeroes. By the way, that is the OPPOSITE of how IFI works…it does NOT write zeroes, which is why it’s so much faster. You have it 100% backwards.
For once, I’d love to see DBAs justify their SQL practices to Jeff Besos and/or a CTO of a data center. You’d be laughed out of reality with real-world data and nothing to argue with other than “this is how we always did it”..great. Tradition. Good thing it’s busted.
We run these on Solid State Drives, LOTS of them! Growing a logfile is trivial these days…laughably NOT a factor, even in production unless you are doing 100,000 transactions/sec. For anyone other than a massive online presence, if you have an array of accelerated SSD’s (which you will if running on vmware in the VAST majority of cases), there is nothing to cause this dark cloud of massive performance issues.
We’re not running mechanical hard drives anymore, people. My ghetto home test datastore can grow 50GB in 3 seconds.
OH THE HUMANITY!! THE USERS COMPLAINING!
They have NO IDEA what is going on and can’t tell tit from tat.
I do this in a datacenter larger than anyone’s house reading this, 4x over full of racks and racks of servers. If shrinking a stupid DB was a problem, we’d know about it. Let it regrow if it truly needs to, fine, we won’t reclaim the space for over a year. But, the VAST majority of massive growths are because of improper SQL manipulation…e.g.: copy x data from x table, dump into y db and y table, select on it, dump results into z db table and move on.
By FAR, this is the MOST critical issue facing datacenters today: the SLOP of users who are NOT DBAs having access to SQL.
What can a DBA/Infra guy do but to shrink when these idiots bork and crash a SQL server with their bunk SQL Statements? Leave it bloated in case it happens again or fix the problem at the source and reclaim your space?
“NEVER shrink a DB”
= piss-poor advice for the vast majority of people managing SQL these days; in the cloud.
Nice rant! Unfortunately, you have a few problems with your logic. Yes, IFI is great if the application ALLOWS it to just create the file without writing all 0’s. Unfortunately (fortunately?) for a log growth operation it doesn’t. So IFI doesn’t change a thing in this case. And yes, you are right. At 2am the only things going on are batch processes that have to finish by morning, backups, other general DB maintenance etc, oh, and that report that the CIO has said he HAS to have ready for him every morning. Of course none of that matters because while your shrink may be running at 2am, unless you deliberately grow your DB out the growths are going to be happening while people are USING the DB.
Now, SSDs have dramatically changed the speed at which we work, our expectations have changed too. A user doesn’t want to wait 30 seconds for a response. They want it instantly. And while you may not care if the users are complaining you can bet Jeff Besos does.
Sending 46gb instead of 400mb? Please tell me that your database isn’t >100x the size it needs to be? Of course 46gb is pretty tiny so maybe it is. Regardless, why would you be sending the database files? Wouldn’t you take a compressed backup and send 100mb?
And last but not least this isn’t a fear based warning. This is fact based, knowledge based, and even more importantly experienced based. Because here’s the thing. I’ve studied databases, I’m GOOD at SQL Server. I’ve played with, tested, and worked with databases my entire career. That said, if an experienced sysadmin tells me something about their specialty? Or a dev tells me something about their’s? I’m going to give them the benefit of the doubt because it’s THEIR specialty. Not mine. And I expect them to give me the same respect when I tell them how a database works.
Congrats on joining the piss-poor-idiots club! What took you so long?
Probably because “I’m Just That Good”™
Too funny. Some people think they know it all to the point that they are confidently wrong. Unfortunately customers listen to this hogwash… which results in us consultants having more… silly… work than we should…. but makes us look like heros when the company “genius” thinks he knows what’s going on under the covers. Kenneth is absolutely right… Vince? You obviously need to listen to more of these people before you’re mouth gets you into more trouble.
[…] I realize you shouldn’t shrink your database (data files or log files), but I recently had a case where it was completely appropriate to do a shrink. We had a table that […]
[…] to take that huge log backup or a full one for that matter. You need to clear out the log, get it shrunk and move on (with regularly scheduled log backups […]