I recently performed a health audit for a client and found several transaction logs that had grown exponentially larger than the data files. This is a fairly common thing to find when databases are in full recovery model with no log backups, however that was not this client’s issue. The databases were in full recovery and log backups were being taken every 15 minutes, however the log was not truncating and reusing the existing log.
I looked for open transactions assuming that was the issue but that was a dead end. I decided to do some digging and looked at the database properties in sys.databases and found that the log_reuse_wait_desc was set to ‘REPLICATION’. This was odd since the client stated they do not use replication. I reported what I found to my client and was told that a long time ago they toyed with replication trying to build a reporting server and ended up killing the project.
What didn’t happen was cleaning up and removing replication from the databases once the project was cancelled. I am not a big replication guy so this was a new area for me. With a quick search I was able to find sp_removedbreplication which removes all replication objects from the database. I was able to remove replication from each of the database by running the following:
sp_removedbreplication N’db_name’
Once replication was removed the log_reuse_wait_desc was updated to ‘nothing’ and the log was able to truncate. This didn’t solve all the problems with these logs though. An adverse effect of the logs growing out of control were extremely large log files with a lot of virtual log files. I followed Kimberly’s advice in her post (8 steps to better transaction log throughput) and was able to shrink the log file, resetting the number of virtual log files, and then properly grow the log files to efficient sizes with more appropriate auto growth sizes.
BTW, for an interesting twist on the REPLICATION value for log_reuse_wait_desc, see Paul’s post: REPLICATION preventing log reuse but no replication configured.