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:
- A Full backup daily at 4:00AM
- Transaction Log backup every 2 hours
- Backup Cleanups to remove anything older than 1 week
- Rebuild index weekly on Sundays
- 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.