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)).

Advertisements