We are facing an issue with the Log Drive (E:) getting filled up frequently.
Our DBMS is SQL Server 2014 SP1 with AOAGs set up.
We have weekly maintenance tasks:
- Rebuild Indexes (Runs close to 1 Hour)
- Update Statistics (Runs Cloe to 30 Minutes)
- Check DB runs close to 30 minutes
- Full Backup runs close to 4 Hours
- Differential runs close to 2 Hours
We are not seeing any heavy transaction processing.
But we can see E: getting filled up.
Below are the Auto-Growth Settings:
- 10% Growth with unlimited.
Note: Every 30 minutes we have transaction log backup scheduled as this is AOAG environment.
My questions:
-
In this case, what is making the E: getting filled up?
-
Why is my Log not getting truncated?
-
Do we need to expand E: in this case?
-
What should be the action plan to get my log truncated?
As of now we are shrinking file now and then to free up space before we decide next plan of expanding drive.
Please suggest.
Best Answer
I suggest you read this, as this is applicable in all scenario (even you have Always On Availability group) when your database is in full recovery model .
Why Does the Transaction Log Keep Growing or Run Out of Space?
Yes you should implement transaction log backup immediately. Read this for details.
Best way to backup & truncate transaction logs on a schedule
Shrink will not help as your logs are not being truncated. If you are truncating manually and shrinking you will not be able to recover point in time. Moreover files will grow again and you have to keep doing this manually all the time. Once you pre grow your file based on your usage and set up scheduled transaction log backup you should not have to adjust file size, truncate manually or shrink file.
After you set this up then you can decide if you need to expand your E drive.
I also suggest change your transaction log autogrowth from percent to a fixed size. Read this.