I recently had a situation where a database that was set to SIMPLE recovery mode had an ever-growing log file. I had tried a number of things to get it to shrink (i.e. running CHECKPOINT: and the DBCC SHRINKFILE(…) to no avail. I know that the log file should be truncated every few minutes due to the CHECKPOINTs SQL Server regularly issues, unless there are open transactions. I then tried running:
|
1 2 3 4 |
USE MyDatabase GO DBCC OPENTRAN('MyDatabase') WITH TABLERESULTS |
This is what I got:
 |
I then tried running the statement below:
|
1 2 |
--if the log_reuse_wait_desc is replication then remove it SELECT name, log_reuse_wait_desc FROM sys.databases |
This query showed that the database was marked for “Replication”. Replication existed on this server, but not for this database. We have not used replication for a long time now (we use log-shipping for our needs). I ran the following command to drop the database for replication:
|
1 2 |
-- this will remove replication for the database specified EXEC sp_removedbreplication 'MyDatabase' |
I then re-ran the command to verify that it is no longer marked for replication:
|
1 2 |
-- log_reuse_wait_desc for the database should change to "nothing" SELECT name, log_reuse_wait_desc FROM sys.databases |
Now that it is no longer marked for replication, we can shrink the log file. If you need to set the recovery mode to SIMPLE, use this:
|
1 2 |
ALTER DATABASE MyDatabase SET RECOVERY SIMPLE GO |
…now shrink the log file:
|
1 2 3 4 5 |
-- issue a CHECKPOINT CHECKPOINT; -- shrink the log to 10MB. Make sure you use the logical log file name DBCC SHRINKFILE (N'MyDatabase_log', 10) |
After executing this command, confirm that the log was shrunk from whatever it was to 10MB. You can use the command below:
|
1 2 3 4 5 |
USE MyDatabase GO EXEC sp_helpfile GO |
Don’t forget to ALTER the database back to FULL recovery model if it needs it.
NOTE
I have read MANY blogs that stated the following command will fix the issue:
|
1 2 3 4 |
USE [MyDatabase] GO EXEC sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1 |
…however, it seems that this will temporarily fix the issue but it will continue as it simply marks the log entries as replicated. It does not resolve the real issue of removing the database from replication.
Here is a good forum posting on this issue: log_reuse_wait_desc = replication, transaction log won’t stop growing
Views – 2388