2

Just trying to gather some knowledge on this topic as I have seen numerous answers and they are just confusing. I am using SQL Server 2012/2014 EE.

Is it a good idea to shrink transaction log files? If yes, then I see a lot of people say that back up your transaction log and then shrink. We have a backup tool that does a full backup @ 9:00 PM and does transaction log backup every 3 hours, if I have to shrink a log file, then I will make a manual backup and shrink the file? Correct?

I believe this is going to break up the log backup chain of our back up tool. How should this be handled?

If the answer is No to shrinking the log file, then what is the solution to transaction log drive filling up?

Also, do I have to take any special consideration when shrinking transaction logs of the databases which are in mirroring or Always On?

I am confused about the tlog backups as well, what do they do? What I mean is that I have a database whose log file space (after taking a log backup) is 20386.00 MB and the available free space is 20256.09 MB (99%). This makes it look that the entire TLog drive is full, however when I shrink this file then the 20256.09 MB is released to OS.

The "Corporate" policy does not allow me to change the timing of log backups. I have 20 gigs on the server for transaction logs files. There were some batch processes running which caused the transaction logs to grow to full 20 GB and since there is no more space left the database went down. This is why I need to shrink the files and release the space to OS so that database can come up.

3

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.

1

I'm going to preface with the thoughts that you should do some in depth research on the topic and try to apply it to your real world application and process. The answer is typically to most of your questions, "it depends".

Is it a good idea to shrink transaction log files?

  • Yes

    • Your drive is full and you need immediate disk space back or your drive is going to become full causing outages.
  • No

    • It is going to grow again and it could take time and resources to grow.

So ask yourself, which is worse and which circumstance can you live with? I'll address some alternatives below with the rest of the questions.

if I have to shrink a log file, then I will make a manual backup and shrink the file? Correct?

You need to either take a backup if you are in simple mode or you need to do a T-LOG backup if you are in FULL mode. Then run the shrink file cmd.

Notes on SIMPLE mode backups:

This can take a long time, depending on the size of the database. It can be more optimal for you to take a checkpoint instead when you are in SIMPLE mode and that is recommended.

I believe this is going to break up the log backup chain of our back up tool. How should this be handled?

Have your tool take the T-LOG or SIMPLE mode backup. You should probably address this with your tool tech support. My thought is, do it manually and then force the application to take a new FULL backup creating a new log chain once you are finished with your shrink. You should consult your tools support first though to ensure this will not cause issues.

If your answer is No to shrinking the log file, then what is the solution to transaction log drive filling up?

If in SIMPLE mode, consider using CHECKPOINT, if you are in full mode, consider switching to BULK-LOGGED while performing certain operations and switch back (make sure to use best practices here) or using utilities like TRUNCATE or BCP that create less transactions in the T-LOGS.

Also, take more frequent backups if your environment allows. (If you are in SIMPLE, try CHECKPOINT instead since it's a minimally logged operation.) If in FULL mode, take more frequent T-LOG backups. Otherwise the drive is filling up for a reason, why is it filling up? Are you doing more work now?

Perhaps the drive needs an increase in size because it's going to keep filling up. Maybe someone ran a heavy transaction that filled the log and it won't happen frequently, so this could be a one time thing. Put alerts in place so you know before it reaches critical mass.

Do I have to take any special consideration when shrinking transaction logs of the databases which are in mirroring or always on?

Maybe.

SQL Central Forum on AG / Mirror T-LOG Shrinking

The cloud technologist: Joe Stocker: on AG / Mirror T-LOG Shrinking

Here are some resources you should read in their entierty:

Brent Ozar and many resources on the issues of T-LOG Shrinking.

Brent Ozar on T-LOG backup frequency.

I see backing up of transaction logs come up in every answer. I am now confused about the tlog backups as well, what do they do?

I think of data and log files as buckets. If I fill my bucket with water and I want to carry more water, I need a bigger bucket. If I lose or remove some of the water, I still have the same sized bucket, but now I have available space within it.

SQL Server creates the MDF or LDF (bucket) and fills it, then when you take a T-Log backup, it removes the data (water) from the LDF. Your LDF is still the same size on disk, but it has space for more data without requiring growth until it is full again. Tlog backups are truncating the LDF and inserting the active data to the tlog backup file.

0

I dont shrink trans log, on regular basis. I try to keep log as large, as it needs to be. You dont want your log to expand every day, and to shrink it with your night job. If you need some goal on this, you can try to keep low number of VLF (virtual log files), lets say 25.

What you can do is, to note current size of trans log, shrink it to minimum size that you can, then expand it to 10% larger size then it was before shrinking. That way you can have large enough trans log, for your daily workload.

After this you must track number of VFL in trans log. If your log starts to grow (and the number of VLF's), than you must repeat procedure.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.