"Huh. I didn't know I'd discussed my blog with you," I said, surprised at the easy way he'd added my nom du blog to his vocabulary.
"You have a blog? Anyway - you've got that look on your face... no, not that one, the other one," he continued, because my expression had changed to one of exasperation.
"You know, back in the 20th century, some other weirdo came up with the notion that 'I have no head'...". This is true - and it's a fascinating point. We never see our own faces - we see evidence of our faces, in mirrors and photographs, but in a real sense, we're in our brains, a point looking out, and... oh, never mind.
My co-worker, who was fictionally quick on the uptake, said "The expression you get on your face when people talk about shrinking transaction logs!"
"Oh, that! A combination of hopeless despair and frustration with the state of humanity...".
He was right. I did have "that look" on my face. See, I'd been discussing transaction log sizing with an equally fictional customer. His transaction logs were supposed to be 10gb, you see. And they grew to 15gb, so he shrunk 'em back to 10, and they grew to 18, so he shrunk 'em back to 10, and they grew to 17, so he shrunk 'em back to 10, and then they grew to 21gigabytes, so this time, he wasn't doing any more fooling around. He shrank them back to 5 gigabytes.
Some of you are boggled, I'm sure - but yes, this does happen. Some people see their log files grow, and assume the answer is to shrink 'em back.
Now, I hope you all know that you never want to shrink MDF file if you can possibly avoid it. If you have to remove an MDF file, a SHRINKFILE with the EMPTYFILE option is fine, but the damage a file shrink can do to your indexes could bring tears to your eyes - or maybe it's the user complaints about the performance hit from the massive disk activity plus the index fragmentation.... Anyway, sure, if you have no choice, shrink the MDF, but rebuild those indexes ASAP.
Shrinking a transaction log can take on a whole new dimension of
Okay: but that's data files. Log files have to be initialized. SQL has to write the correct headers and bit patterns to the new transaction log. If you want your log to grow by a gigabyte, you need to do a full gigabyte of writes to disk. And let's face it: far too often,
You really want to avoid growing log files in the middle of ordinary server operations. Oh, you want to have file growth enabled - a big slowdown while a log file grows is better than the log file filling and everything coming to a halt! But you want to get ahead of the game, and prevent it from happening (except during maintenance) if you can.
And so, if your log file has grown, you really need to stop and think before you shrink. If your log file has grown to 20 gigabytes, that means there was at least one brief moment where SQL needed somewhere in the neighborhood of 20 gigabytes of log space for smooth functioning. So, sometimes, your server needs that much log space. Is there truly some reason you think it will never, ever need that much space again?
Did your log backups fail for an extended period of time? Okay - if that happened, go ahead and consider shrinking them back. Similarly, if you only took log backups once in a heck of a long time, and are now taking them far more frequently, go ahead, consider shrinking. Heaven help us, if you decided you're always throwing away your log backups, and changed to SIMPLE recovery model because it suits your needs, again, feel free to shrink 'em. (SIMPLE recovery model doesn't get enough love, so I like sending it some; when it's the right tool, use it!)
Were you performing something huge, like a fully logged index rebuild (or more than one), or a bulk insert (if so - why didn't you set to Bulk_Logged?[2]), neither of which you'll have to do for a long, long time? Sure, go ahead and shrink them back.
But in a great many cases, if your logs grow to 20gb, your database needs 20gb. And you should leave them at 20gb... eventually at least.
"Eventually?" you ask. Thank you! It's nice to have helpful, questioning readers! Yes, if your log files have grown in lots of tiny increments[3], you have a lot of VLFs - virtual log files. For some reason[4], SQL Server doesn't like to have more than a few dozen VLFs. It can handle even a few hundred, but once you hit about a thousand VLFs, Bad Things May Happen. You may see an extremely long recovery on startup, when SQL has to enumerate and read all of those VLFs. You may see poor performance in log backups, log restores, or the Log Reader Agent if you use transactional replication. You may see poor performance in mirroring, or with availability groups. And I'll guarantee you, although I've listed all the possible problems that I can recall off the top of my head, there's yet another lurking danger ready to do a SERIALIZABLE SELECT right around your short and curlies....
Okay, maybe I'm getting a bit silly here. Seriously: if your log files have grown more than, say, a dozen times (yes, ever!!!) you probably want to shrink them as small as you can, and then grow them in relatively big chunks - see Kimberly Tripp's canonical article for how to grow 'em best - until they're at a size where they won't have to grow ("at all" if you can manage it; "more than a bit" if you can't). This isn't the most critical thing you'll do today - too many VLFs won't crash your server or slow down most normal operations, most of the time. But they will sneak up on you when you least expect it - so keep them under control.
Get your log files to a good size. Leave them there. And keep them from growing more than once in a while.
[1] Seriously. You might be surprised or appalled at how often you'll see poorly performing disks "in the wild", plus how poorly they might perform.
[2] Yes, there are several good reasons not to go bulk-logged. I'm just checking to see if your answer is one of the good ones!
[3] Or lots of large increments, if your log file's really, really huge - really, it's the number, more than the size, that matters.
[4] "for some reason," as in, there's a reason, but I'm darned if I know precisely what that reason is.
