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.

1 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.

  • Thanks! Not sure of the type of disk, but all files are on a SAN and probably of the same type. You're right, it's not a super transactional application. Should be able to find out the time for the full backup so will be able to estimate the log backup time. I'm not under resource constraints re disk usage, but I need to restore the database to a test environment. I'm leery about doing that with such a large file. Am I just being overly worried? Feeling a lot happier now though, so thanks again! When we're back on track should I consider throwing differentials into the mix? Nov 20, 2022 at 4:48
  • @DaveHarding No problem! "I'm not under resource constraints re disk usage, but I need to restore the database to a test environment. I'm leery about doing that with such a large file." - What's there to worry about?...If you have ~400 GB of space to spare on the test system then there's nothing to be concerned of, especially on a test system. If you don't have the space to spare, then you'll want to SHRINK the database after you get the backups to run, so you reduce the Transaction Log down to a reasonable size. (But again SHRINKs are heavy, so don't do this normally outside this fix.)
    – J.D.
    Nov 20, 2022 at 5:05
  • 1
    @DaveHarding "When we're back on track should I consider throwing differentials into the mix?" - I've worked on databases as small as yours, and as big as almost 2 TBs. I've never had a need for Differential backups. In theory, they help offload some of the work of Full backups, by reducing the need for Full backups as frequently, on large and busy systems. But I've always been fine with nightly Full backups and frequent Transaction Log backups. For your database, I see no need for Differentials.
    – J.D.
    Nov 20, 2022 at 5:07
  • @J.D. On our similar sized busy OLTP database, 120Gb of data, but diffs are 100s of Mb to a few Gb later in the day. We do full daily, four diffs, and logs every 15 min, the diffs primarily reduce the amount of time taken to run a restore as we can skip out all the earlier logs. Nov 21, 2022 at 1:03
  • 1
    Thanks for the additional comments. As far as data files go that's a whole mess in itself I think. There are 8 or 9 separate data files with greatly differing sizes and usage percentages. No idea why - but I've only been at this job three days! I'm not planning to shrink or change anything file wise that I don't need to. Just taking each issue one at a time. A lot more databases to tackle, probably with similar problems, and I'll be analyzing them all this coming week. Nov 21, 2022 at 3:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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