1

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.

1
  • 1
    Run this query: SELECT name, log_reuse_wait_desc FROM sys.databases and see what it is reporting as the reason the log space is being held. – HandyD Apr 16 at 3:12
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.