Sql-server – Truncation of transaction logs

Under normal operating conditions, shrinking your transaction log file is not recommended. The file grows large enough to handle your current active transactions (with SIMPLE recovery model), or to handle transactions between your transaction log backups (with FULL recovery model).

Unless something very unusual has happened, your shrinking the transaction log file simply means that it will have to grow again.

If your transaction log file is filling up your drive, you should look into the following:

• If your database is in the FULL recovery model, do you have regular transaction log backups running? Are they running to a different drive/server? Can you increase the frequency of the transaction log backups? (This last will increase the frequency with which completed transaction activity is released from the log, so the log file should not need as much space)
• If your database is in the SIMPLE recovery model, do you have very large transactions running? Can you break those transactions into smaller chunks?
• Can you add space to that drive? Alternately, can you add a new drive, so you can add a second transaction log file there?

That said, if you have had an unusual event:

• A database in the FULL recovery model where transaction log backups were not being taken;
• a log file that grew beyond its normal size due to a rare bulk load of data;
• a database that has permanently been switched from FULL to SIMPLE recovery model, where transaction log backups had been done relatively far apart (for instance, once a day).

If you are going to try to shrink a transaction log file, it is easiest to do so immediately after a backup has been taken. If backups are taken regularly, then you don't need to run a backup manually; you can simply wait until a backup completed, then try to shrink the file.

If you don't know what's a good size to shrink to, take your best guess based on the activity that expanded the file - if you have a 350GB transaction log file after running for about a year with no transaction log backups, for example, I'd start with 512 MB. I'd also set the growth factor for the transaction log to a specific value, rather than X% of the current value. In general, I'd aim for about half the starting size, but that's just me.

And, yes, I do believe there are special considerations when the database is involved in mirroring, replication, or always-on; however, I'm not certain what they are.

To expand a bit on what a transaction log backup does:

The transaction log is a record of every transaction that's been committed to your database, as well as any transactions currently running.

• Under the SIMPLE recovery model, transaction log data no longer is needed once the transaction is committed. At that point, the space in the log file that's taken up by that transaction can be reused.
• Under the FULL recovery model, transaction data is kept in the log file until both the transaction is committed, and the data has been backed up via a transaction log backup.

Transaction log backups allow you to perform "point-in-time" recovery.

If your database crashes at 5PM on Friday, and you take a full backup on Sundays, then you have lost all the data for the past week. Even if you take a nightly differential backup, you've lost all of Friday's data. If you run a transaction log backup every 3 hours, then the most data that you will lose would be three hours' worth.

With transaction log backups, you can also choose not to restore through the point where the last backup was taken, but to a specific time before that (hence, "point-in time"). If someone did something to the database 4 hours ago that basically destroyed it, then you want to restore your full backup, then your transaction logs up to 4 hours and five minutes ago. The transaction log includes information on exactly when each change was applied, so that will get back your database to how it was just before this disastrous change was made.

You mention that after a transaction log backup, you looked at your log file and it had about 20GB of unused space; if you shrink it, that space is indeed released from the file, and available for other uses.

However, I'd try looking at how much space is free in the file right before each backup happens. If there's less than 1 GB left free? Leave the file at 20 GB. You see, if you shrink it to, say 100MB, but it really needs 20 GB every three hours for your transaction load, then the system will simply need to reclaim that20 GB over the course of the next three hours. This is bad for 2 reasons:

• SQL Server takes some time to add space to the log file; unlike data files, this cannot be done "instantly" by turning on an option.
• If a 20 GB log file has eaten up all the space on your disk, and you've shrunk it to 100 MB, what happens if someone else drops a 10 GB file onto your hard drive a hour after you shrunk the log file? Around 30 minutes later, anyone trying to make a change to the database will get an error, because the log file is full.

Even if the log file has 19 out of 20 GB free at the end of an hour, you still may not want to shrink it down to (say) 2 GB. Something made it grow to 20 GB in the first place. You may have a job that runs overnight, or once a week on the weekend, that creates a lot of transactions, with a lot of data. If that is happening on a weekly basis, you'll need that 20 GB at least once a week. Why shrink the file to the 2 GB it needs Monday to Saturday, when every Sunday it will have to grow back to 20 GB? You've still got the two problems I mentioned above, and if you do run out of log space while some major off-hours process is running, it may take more than just the time to free up space for the log to resolve the problem fully, which might just ruin your weekend. Or, even worse, you don't find out there was a problem until Monday morning, and this system may be functionally down for users for most of the day!

By the way - If your transaction log does record 20 GB of changes in 3 hours, I'd want to be taking my transaction log backups much more frequently: maybe every 5 minutes.

See also: The Transaction Log (SQL Server) in the product documentation.