Shrink a log file…AUTOMATICALLY?

Today let’s talk about shrinking log files, and Minion Backup – still totally & forever free – and how they go together.

First things first: why would you want to shrink a log file in the first place? Haven’t we heard that shrinking files is bad? Well, no and yes. No, shrinking log files – when you really need the space, only once in a while – is not a bad thing at all. Shrinking log files frequently, or shrinking data files, or having auto shrink turned on? Yes, those things are bad.

But back to that first scenario: Any of a number of things – a really long transaction, mirroring, replication, etc. – can cause your log file to expand, and expand, and expand. When your log file is unusually large, and you’re short on disk space, that’s when you’d really want to shrink your log file.

The attack of the 90GB log file

So let’s say every nine weeks or so, you see a really large transaction that fills your “I’m normally a little 5 GB-er” log to 90 GB. After that transaction ends, the next log backup truncates the log file, and you’re left with a mostly empty 90 GB log file, and only 2GB of space free on the disk!

Right now, maybe you discover the situation with a disk alert – if you have such a thing set up. Or a not-nearly-as-good scenario is, maybe you only hear about the problem when the disk fills up and people start yelling at one another and running in circles. In either case, you have to connect to that server, check the log space usage, and issue the shrink command yourself.

If this sort of thing happens on even a semi-regular basis, you’re going to want to automate the shrink.

MB feature: Shrink the log file when you take a log backup

With Minion Backup, you can configure your log backups to shrink your log file automatically. (Yes, I said you can configure it…we do NOT want to automatically shrink all your logs for you by default! That just seems invasive, somehow. Autoshrink-y, even.)

It’s a simple setup: configure the backup settings, and then have a hot beverage.

Configure the backup settings: Configure your settings in Minion.BackupSettings. For this example, we will turn on and configure “shrink log on log backup” at the default level:

UPDATE  Minion.BackupSettings
SET  ShrinkLogOnLogBackup = 1
  , ShrinkLogThresholdInMB = 1024
  , ShrinkLogSizeInMB = 2048
WHERE DBName = 'MinionDefault';

 

Aaaaaand, we’re done. But let’s go over what each column is for:

• ShrinkLogOnLogBackup – Note the wording: shrink log on log We do not want to shrink the log after a full or a differential backup, when the log hasn’t been truncated (and there may be little or no empty space to shrink anyway). We should only ever shrink the log file after log backups.
• ShrinkLogThresholdInMB – This sets the log shrink threshold: how big do you want the log file, before the shrink action kicks in? This is a measure of the file size, regardless of how full it is. Here, we’re saying we only want to shrink the log file if it’s 10GB (10240MB) or more.
• ShrinkLogSizeInMB – This is the target size: how small you want your log file to be, ideally, after the shrink operation. Note of course that if the log contains 5GB worth of data, of course it won’t make a 2GB target, but that’s fine…it’s just a target size.

Now, whenever a log backup runs, Minion Backup will run a shrinkfile statement for the log file IF AND ONLY IF that log file is over your ShrinkLogThresholdInMB size (in this case, 10240MB/10GB).

Bonus: Seriously thorough logging

All backup actions are logged to tables in Minion Backup, along with a bunch of related data – like, for example, the settings and results of the log file shrink action. This gives you (just for starters) a history of how often the log shrink happens. Take a look, if you have Minion Backup running already:

SELECT  ExecutionDateTime
, STATUS
, PctComplete
, DBName
, BackupType
-- Shrink log info:
, ShrinkLogOnLogBackup
, ShrinkLogThresholdInMB
, ShrinkLogSizeInMB
, PreBackupLogSizeInMB
, PreBackupLogUsedPct
, PostBackupLogSizeInMB
, PostBackupLogUsedPct
-- Log use wait info, VLFs:
, PreBackupLogReuseWait
, PostBackupLogReuseWait
, VLFs
FROM  Minion.BackupLogDetails
ORDER BY ExecutionDateTime DESC;

(Notice that “PctComplete” column? Yeah, you can totally watch your backups progress as they run. Live Insight FTW!)

Look at the applicable “shrink log” columns in the Minion.BackupLogDetails table:

• ShrinkLogOnLogBackup – A record of what setting was used.
• ShrinkLogThresholdInMB – A record of what setting was used.
• ShrinkLogSizeInMB – A record of what setting was used.
• PreBackupLogSizeInMB – The size of the log file before backup (in MB).
• PreBackupLogUsedPct – The percentage of the log file that was used, before the backup.
• PostBackupLogSizeInMB – The size of the log file after backup (in MB).
• PostBackupLogUsedPct – The percentage of the log file that was used, after the backup.

The log also gives you the log use wait description – straight out of sys.databases – before and after the backup. This gives you the ability to troubleshoot, alert on, and report on log issues.  How many times was the log waiting to be truncated and reused because of a transaction? Or replication? And so on.

A brief side note here: We’ve used the word “truncate” a couple of times, but let me be clear: You should always maintain your logs – thereby truncating your logs – using log backups. If you actually truncate your log file (no I’m not going to tell you how to do it) without taking a log backup, you’ve broken your log backup chain, and your log backups will be useless (from a recovery standpoint) until your next full or differential backup. So in short: take log backups, don’t truncate logs.

The point is, you get a lot of logged data with your backups, and that is wonderful.

MB gives you everything you need to monitor and plan. So if you see the log growing daily, and constantly shrinking, then raise up your ShrinkLogSizeInMB.  Remember: with every log file shrink, you’re using up extra IO, and waiting to zero out all that new space every time the file grows. If the log file is going to keep growing all the time, you might as well leave it large.

Two more examples: Let’s get database specific

Example 1, we don’t want the DB10 log file to shrink. You can make exceptions for individual databases. In our example above, we configured the log shrink for the “MinionDefault” level, which is the default. So that shrink behavior applies to every database right now.

We don’t WANT it to happen for DB10 though! So we insert a row for DB10, with different settings, to except it from the default behavior.  Let’s assume that most of our settings for DB10 are the same as for the MinionDefault row…we just want to configure no shrinking.

INSERT INTO Minion.BackupSettings
( [DBName], [Port], [BackupType], [Exclude], [GroupOrder], [GroupDBOrder], [Mirror], [DelFileBefore], [DelFileBeforeAgree], [LogLoc], [HistRetDays], [MinionTriggerPath], [DBPreCode], [DBPostCode], [PushToMinion], [DynamicTuning], [Verify], [PreferredServer], [MinSizeForDiffInGB], [DiffReplaceAction], [LogProgress], [FileAction], [FileActionTime], [Encrypt], [Name], [ExpireDateInHrs], [RetainDays], [Descr], [Checksum], [Init], [Format], [CopyOnly], [Skip], [BackupErrorMgmt], [MediaName], [MediaDescription], [IsActive], [ShrinkLogOnLogBackup], [ShrinkLogThresholdInMB], [ShrinkLogSizeInMB], [Comment])

SELECT   'DB10' AS [DBName], [Port], [BackupType], [Exclude], [GroupOrder], [GroupDBOrder], [Mirror], [DelFileBefore], [DelFileBeforeAgree], [LogLoc], [HistRetDays], [MinionTriggerPath], [DBPreCode], [DBPostCode], [PushToMinion], [DynamicTuning], [Verify], [PreferredServer], [MinSizeForDiffInGB], [DiffReplaceAction], [LogProgress], [FileAction], [FileActionTime], [Encrypt], [Name], [ExpireDateInHrs], [RetainDays], [Descr], [Checksum], [Init], [Format], [CopyOnly], [Skip], [BackupErrorMgmt], [MediaName], [MediaDescription], [IsActive],
/*** Here are the shrink columns! ***/
0 AS [ShrinkLogOnLogBackup], 
NULL AS [ShrinkLogThresholdInMB], 
NULL AS [ShrinkLogSizeInMB],
'DB10-specific row, no log shrink.' AS [Comment]
FROM Minion.BackupSettings
WHERE   DBName = 'MinionDefault'
AND BackupType='All';

And there we are. Now DB10 will get all of its settings from the new row, and so it won’t ever have an automatic log file shrink. (That’s a rule in Minion: once a config – like BackupSettings – is at the database level, you never drop back down to the default level for any reason.)

Example 2, just shrink the log of one database. Now we’ve totally changed our minds. Instead of setting log shrink at the default level, we want to instead set it up just for one or two specific databases. Well first things first…we need to turn off log shrink for the MinionDefault row.

UPDATE  Minion.BackupSettings
SET  ShrinkLogOnLogBackup = 0
, ShrinkLogThresholdInMB = NULL
, ShrinkLogSizeInMB = NULL
WHERE DBName = 'MinionDefault'
AND BackupType='All';

The one database that really needs a semi-regular log shrinking is SetecDB. In that case, we need to (“Anyone? Anyone? Bueller? Bueller?”) insert a row for SetecDB into Minion.BackupSettings. For the sake of argument, we’ll say that the log shrink settings are the ONLY difference from the MinionDefault settings.  We can pull everything else from that base row:

INSERT INTO Minion.BackupSettings
( [DBName], [Port], [BackupType], [Exclude], [GroupOrder], [GroupDBOrder], [Mirror], [DelFileBefore], [DelFileBeforeAgree], [LogLoc], [HistRetDays], [MinionTriggerPath], [DBPreCode], [DBPostCode], [PushToMinion], [DynamicTuning], [Verify], [PreferredServer], [MinSizeForDiffInGB], [DiffReplaceAction], [LogProgress], [FileAction], [FileActionTime], [Encrypt], [Name], [ExpireDateInHrs], [RetainDays], [Descr], [Checksum], [Init], [Format], [CopyOnly], [Skip], [BackupErrorMgmt], [MediaName], [MediaDescription], [IsActive], [ShrinkLogOnLogBackup], [ShrinkLogThresholdInMB], [ShrinkLogSizeInMB], [Comment])

SELECT   'SetecDB' AS [DBName], [Port], [BackupType], [Exclude], [GroupOrder], [GroupDBOrder], [Mirror], [DelFileBefore], [DelFileBeforeAgree], [LogLoc], [HistRetDays], [MinionTriggerPath], [DBPreCode], [DBPostCode], [PushToMinion], [DynamicTuning], [Verify], [PreferredServer], [MinSizeForDiffInGB], [DiffReplaceAction], [LogProgress], [FileAction], [FileActionTime], [Encrypt], [Name], [ExpireDateInHrs], [RetainDays], [Descr], [Checksum], [Init], [Format], [CopyOnly], [Skip], [BackupErrorMgmt], [MediaName], [MediaDescription], [IsActive],
/*** Here are the shrink columns!***/
1 AS [ShrinkLogOnLogBackup], 
100 AS [ShrinkLogThresholdInMB], 
15 AS [ShrinkLogSizeInMB],
'SetecDB-specific row, to customize log shrink.' AS [Comment]
FROM Minion.BackupSettings
WHERE   DBName = 'MinionDefault'
AND BackupType='All';

 

There we go. Now SetecDB is the only one set up with log shrinking.

The final word: Shrink log files with caution, and data files not at all

If you find that Minion Backup is shrinking log files very often, you’ll want to raise the ShrinkLogThresholdInMB, the ShrinkLogSizeInMB, or both.

So, why is it an option to shrink log files, but not data files? Because shrinking your data file is almost always a bad idea. It causes fragmentation, for one thing.  (Yes, you can run index maintenance right after a shrink, which may use up more data and cause the file to grow again. Whee!)  And if your data file is growing, it’s not because of a large transaction; it’s because you have more data in it. Databases tend, on the whole, to grow, not to grow and shrink and grow and shrink and grow and shrink. So no, shrinking data files is not a feature in Minion Backup. Here’s a bit more on the subject from Gail Shaw.

Too long? Didn’t read? Summary: Set up automatic log file shrinking in Minion Backup, but only for unusual log-growing events. And leave enough space in the file for normal use, okay?

Please enjoy this, and many, many other features of Minion Backup. It’s all on the house.

(What’s that? You’d like more of the same, but in video form? Okay, here’s our Shrinking Logs video.)

Edit: Yes, I really do know that 10GB is 10240MB, not 1024MB.  So that typo is corrected!