This question already has an answer here:
I'm having an issue with a SQL Server log file which will not maintain a steady amount of space held on a HDD which it has been issued. The drive is ~50GB and the log file is growing to occupy this quite often.
The database is set to FULL recovery mode, with hourly differential backups. I understand that this should allow the log file to truncate and use space already allocated to it?
When checking the status of the log file using
DBCC LOGINFO it returns that the status of most VLF's within the files are set to '2' which shows them as being active? We've 537 VLF's in the log file. I don't know why all these would be active at one point using 15GB of space?
I've checked the replication through
DBCC OPENTRAN and it reports that there are no actively open transactions on the file.
Is there a method of maintaining this file I may of overlooked?
Is there anywhere I can go to determine exactly what the log file is doing?
Would replication interfere with truncating/shrinking the log file?
At present, it grows around 1.67GB per day. I've ~18 days before having to perform some emergency work on shrinking the file. I'd like it to stay below 25GB if possible so there is plenty of room to grow if this has a sudden need for space.