How best to resolve excessive Transaction Log size

Question

I understand excessive Transaction Log growth is a very common issue, and has been asked and answered many times. (Why Does the Transaction Log Keep Growing or Run Out of Space?) I also know the exact issue causing the file growth in this particular database. What I'm unsure about is the best approach to fix it with minimal impact to users.

Here's what I've got:

• Database size is 60GB
• Transaction Log is 300GB
• Recovery model is FULL
• Maintenance plans exist to do:
  1. A Full backup daily at 4:00AM
  2. Transaction Log backup every 2 hours
  3. Backup Cleanups to remove anything older than 1 week
  4. Rebuild index weekly on Sundays
  5. Update statistics daily at 1:00AM

These plans have never executed successfully since they were created over 4 years ago due to the Job Owner being a domain account that doesn't have server access. So there has never been an automated backup of data or transaction logs. I can easily fix the jobs by changing ownership, but my concern is what performance impact will I see when trying to fix the transaction log. There was a full backup made manually recently by another user, but I don't know the validity of it. Obviously my first step will be a full backup followed by an integrity check of the backup.

My question then becomes what to do about the transaction log backup? This is a 24/7 database with peak activity during regular business hours. We can tolerate some performance degradation off-peak hours, but anything significant would probably require scheduled downtime.

Should I restart the Transaction Log backup process on the existing 300GB file, or try to shrink it first? What performance hit will the former take? I'd prefer to start the backup to run every 2 hours as intended if it doesn't incur significant increased processing than it normally would have, and then tackle shrinking the file incrementally off-hours as I assume this will definitely have an impact considering the size of the file. This isn't a system that can tolerate downtime more than a few hours so trying to fix it all in one go probably isn't going to be acceptable.

My goal is to get the files to an appropriate and manageable size so that I can backup and restore to a test server, which the previous user tried unsuccessfully to do. I also think this situation is repeated throughout the organization so I'm hoping to get good metrics from this particular task that I can feel more comfortable when I have to repeat it!

Edit: Forgot to mention server specs. It's a VM machine running Windows Server 2012 with 4 cores and 16GB RAM. SQL Server 2014 Standard Edition configured with max memory of 8GB. While I was in the office today (Saturday) I did some quick performance queries and both CPU and memory resources being used were very low. I don't understand the I/O metrics yet and wouldn't know if they were good or being negatively impacted by the log file size.

Answer 1

1. Get rid of the maintenance plan for rebuilding indexes. It's basically useless, a waste of resources, and will contribute to your Transaction Log growing which is your main concern.

2. Backups are somewhat resource intensive, but can generally be ran concurrently with other queries and processes for systems that aren't under high I/O load (especially consistently ran Transaction Log backups). You didn't mention the type of disk your database is on, but if it's an NVMe or SSD, and the disk you're backing up to is of similar caliber, then that'll definitely be helpful.

3. If a 60 GB database generated only 300 GB of Transaction Logs over 4 years, then I doubt your database is super transactional (aka not too heavy on the I/O side of things). So you might do ok with running your backups. Unless someone has already shrunk the Transaction Log over those 4 years, you might be in pretty good shape.

4. Definitely pick the best off hours time you can to run a Full Backup and then a Transaction Log backup right after. And fix those maintenance plans / jobs so they can run consistently.

5. The more frequently you run Transaction Log backups, the less data it'll need to backup at one time and ergo will run faster. But of course at the tradeoff of resources being consumed to do the backups more frequently. Again, I don't think your database is super transactional, and it's pretty tiny in size, so your Transaction Log backups should normally be quick (ideally less than a second) when ran consistently. Another bonus is you get more granular points of recovery by taking them more frequently. I run mine every 5 minutes and my databases are a few hundred GB in size each and are decently transactional.

6. If possible, find out how long it took that other user to run the Full backup so you have an idea going into it. The Transaction Log backup will probably roughly take 5x as long (300 GB = 5 * 60 GB).

7. If you guys can afford leaving the Transaction Log file itself on the disk at 300 GB big, then I wouldn't waste resources shrinking the file. One day your database may be big enough or transactional enough that it'll regrow the file to that size anyway. Growth and shrink operations are resource intensive. Just focus on getting the backups to run, which besides clearing out the Transaction Log file internally, are also important from a data recovery perspective.