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?.
Continue reading on SQLStudies.com.