Reduce transaction log file utilisation I have a SQL Server 2017 database that currently has an 80GB data file and a 90GB log file. I want to get the log file down to a more manageable size, because I am about to run some big scripts which will hit the log file hard. The database is also replicated to another database.
If I run dbcc sqlperf('LOGSPACE') then it tells me that utilisation of the log file sits at 99%, and this is preventing me from shrinking the log file down to a more manageable size. For instance, if I run dbcc shrinkfile(mydb_log, 4096) then the error is Cannot shrink log file 2 (mydb_log) because of minimum log space required.
I have tried backing up the database then backing up the transaction log, in full recovery mode. I have tried switching to simple recovery mode, then backing up the database then shrinking the database, but the log file utilisation still sits at 99%.
Does anyone have any suggestions as to what to try next? Is replication likely to be causing an issue? The databases currently sit on a test system, so I can take more extreme measures than if they were in production.
It looks like replication was not set up properly; I'm guessing that that means that when a transaction gets written to the log and replication fails, the log entry doesn't get marked as ready-to-be-cleaned-up, so the entries just pile up and the log utilisation goes up with it.
I deleted all replication configuration, and the log file utilisation dropped from 99% to 0.1%, so now I can drop the log file back to something more manageable.