Monday 26 October 2015

log_reuse_wait: 'Replication' status appearing

After being unable to shrink a specific database log file to a zero (or something near that) I became slightly puzzled why a 30GB log file would only truncate to about 15GB - after some research I found out that sometimes after replication has been turned on and then stopped - the log_reuse_wait value has not changed back to it's default and is still set at '6' - which tells us that the log file witholds some transactions in the log for use with replication.

I ran the following command to retireve log_reuse_wait information for each database:
SELECT name, log_reuse_wait_desc FROM sys.databases

As replicatoin was not currently turned on (although it was at one point) it looks like the termination of the replication went wrong somewhere and so we need to attempt to remove the 'Replication' status:
EXEC sp_removedbreplication my_database;
and then run the following command again to verify the log_reuse-wait is back at '0':
SELECT name, log_reuse_wait_desc FROM sys.databases


Post a Comment