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.