Transaction Log File is filling up on a database with log_reuse_wait_desc set to replication
Had an interesting issue today where a database was set to Simple recovery mode, but had some how filled up the log drive. trying to shrink the log file failed, with an error saying that the file cannot be shrunk and to check the log_reuse_wait_desc (apologies for not have the exact error or an image, I was caught up with a quick resolution and didn’t have time to screen shoot my screen).
Running the below I found that the databases had a wait of “REPLICATION” which was strange as it wasn’t part of any replication topology and CDC was not enabled.
-- View if CDC or Replication is enabled SELECT name , is_published , is_subscribed , is_merge_published , is_distributor , is_cdc_enabled , log_reuse_wait_desc FROM sys . databases WHERE log_reuse_wait_desc = 'REPLICATION'
This occurs because either the database was restored from a publisher database or replication was configured at some point for the database and removed improperly.
the solution is to run the below
EXEC sp_removedbreplication 'dbname'
Of course, please be careful when removing replication. If this database is being used with replication this command removes all replication objects from the database. (read more here: sp_removedbreplication (Transact-SQL)).