log_reuse_wait_desc = replication, transaction log won't stop growing
Hello,

We are using MS SQL Server 2005, version 9.00.3042.00. Our recovery model is set to SIMPLE. We do two different snapshot publications a day in the middle of the night, and the replication agents stop when they are done, I checked.

There are no open transactions on the database in question, although there are around 20 on tempdb. The transactions on tempdb when reading from sys.dm_tran_database_transactions all have:
database_transaction_begin_time = NULL
database_transaction_type = 2 (read-only)
database_transaction_state = 3 (The transaction has been initialized but has not generated any log records.)
database_transaction_status = 0
database_transaction_status2 = 256

This is the result of an OPENTRAN command run on the database in question March 6, 2008 at 1:40 PM.
Oldest active transaction:
SPID (server process ID): 81
UID (user ID) : -1
Name : INSERT
LSN : (999:138204:2)
Start time : Mar 6 2008 1:34:47:827PM
SID : 0x88d52e4051a71143adee5dc7b6619f8a

Replicated Transaction Information:
Oldest distributed LSN : (890:2091888:1)
Oldest non-distributed LSN : (896:2784855:1)

This is the problem:
The transaction log won't stop growing. The log_reuse_wait_desc in sys.databases for this database says REPLICATION, but the replication agents are not running. I know that it worked fine for several days after the replication was set up, when the log_reuse_wait_desc said NOTHING, but I don't know why it changed to REPLICATION. When I run the shrinkfile command, it runs, but the file size remains the same.

Everything I've read says that with a simple recovery model the transaction log should essentially take care of itself, or I should be able to shrink it with dbcc shrinkfile, but that's not happening, so what's the next step?

Any help would be appreciated.

Have a good day.
Dale Buchanan
March 6th, 2008 8:59pm

The fact that you have non-distributed LSN in the log is the problem.I have seen this once before not sure why we dont unmark the transaction as replicated. We will investigate this internally. You can execute the following command to unmark the transaction as replicated

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

At this point you should be able to truncate the log.

Free Windows Admin Tool Kit Click here and download it now
March 7th, 2008 2:44am

Thanks, that did the trick. I set the script as a daily job to run your command and then shrinkfile.

Have a good day.
Dale Buchanan
March 7th, 2008 3:59pm

Hi i have the same problem and i dont see this as a solution rather a workaround, but the applicationpeople breathing my neck for a explanation and possible a solution. So are there a explanation and solution at this point.

Free Windows Admin Tool Kit Click here and download it now
April 29th, 2008 12:42pm

Hi,

"The fact that you have non-distributed LSN in the log is the problem.I have seen this once before not sure why we dont unmark the transaction as replicated. We will investigate this internally. You can execute the following command to unmark the transaction as replicated"

Of course it's a workaround. I spent two days following steps on other posts and it didn't help me. The message above indicates that Microsoft itself didn't know about the problem, which means it is a bug, which means you have to use a workaround until they produce an update to fix it. This is much simpler and less resource-intensive than the other workaround I encountered, which suggested that you should back up the database, then detach it, then get rid of the log, then reattach the database.

Have a good day.
Dale
April 29th, 2008 2:05pm

I have the same problem and I tried torun EXEC sp_repldone @xactid=NULL,@xact_segno=NULL,@numtrans=0,@time=0,@reset=1 but it says database is not published.

Free Windows Admin Tool Kit Click here and download it now
May 21st, 2008 12:57pm

I, also, am getting this error message. I suspect that the database I am seeing this issue with was a backup, move and restore of a replicated database.

Until a fix arrives, I plan on detaching the DB, killing the log file and reattaching.

July 24th, 2008 6:32pm


Has thisbeen identified as a problem within the replication framework and if so, apart from the workaround suggested in prev posts, is a resolution available?
Free Windows Admin Tool Kit Click here and download it now
March 27th, 2009 7:58pm

Hi,

We are facing a similar problem. Our situation is as follows:

We run a DB in full recovery model. Every 15 minutes we backup the log. At midnight we backup the complete DB. There is neither replication nor mirroring configured for any DB on the server.

Truncating the log does not work, and sys.databases states REPLICATION as the reason. However, executing sp_repldone does not work either, as no replication has been configured. Meanwhile we are running into severe disk space problems due to the log size.

So far I do not dare "killing" the log by detaching the DB renaming the log file and re-attaching the DB, as I do not know if I have to fear any severe side effects.

Has anyone a hint what I could do?

Many thanks and my best wishes for a Happy New Year,
Dietmar.
December 31st, 2009 2:22pm

Create transactional replication on the database. just create a new publication with one table and then delete the publication.

You may also want to try executing sp_repldone when you have created the publication.

--Sateesh
Free Windows Admin Tool Kit Click here and download it now
December 31st, 2009 3:06pm

The fact that you have non-distributed LSN in the log is the problem. I have seen this once before not sure why we dont unmark the transaction as replicated. We will investigate this internally. You can execute the following command to unmark the transaction as replicated

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1

At this point you should be able to truncate the log.

So is this an official bug with Microsoft? Or is there a more current resolution?
I'm in a very similar situation as the original poster.  DB is in Simple recovery mode.  It has one snapshot publication, no transactional ones.  Percent of transaction log file used is growing.  Once it reaches ~90% full, the transaction log file will grow in size (auto-growth is set at 100 MB), then the percent of transaction log file used will again climb to ~90%, log file grows again, repeat.  Nightly full backups are taken, but the % of log file used never goes down, log file continues to grow.   Log_Reuse_Wait_Desc in sys.databases is "Replication".  Dbcc opentran returns this:

Transaction information for database 'XXXX'.

Replicated Transaction Information:
        Oldest distributed LSN     : (139564:680:6)
        Oldest non-distributed LSN : (139564:839:1)

Manually running sp_repldone will temporarily fix the situation - log file % used goes to near 0 and transaction log file can be shrunk.  But I am quickly returned to the situation described above.  Is this still the only option?  In testing various other suggestions, I did notice that creating a transactional publication does result in the log file not growing, presumably because the Log Reader Agent is running sp_repldone.  But if I only have a snapshot publication and in simple mode, why are transactions staying in the transaction log file?  I shouldn't need to manually run sp_repldone (as was provided as the workaround), or create a transactional publication so that a LogReaderAgent will run that SP.  Has anything regarding this issue changed since it was initially brought up in March 2008?
January 7th, 2010 7:44pm

this worked for me! This problem kept reoccurring every few months.

Also an excellent article found here

http://blogs.msdn.com/sqlserverfaq/archive/2009/06/01/size-of-the-transaction-log-increasing-and-cannot-be-truncated-or-shrinked-due-to-snapshot-replication.aspx

As a long term fix its says to turn off "Replicate DDL changes" option on the publication. This should not be needed for Snapshot replication anyway so its easily done. Just need to wait and see now if this works as a long term solution.

...until MS comes up with a proper hotfix that is
Free Windows Admin Tool Kit Click here and download it now
February 11th, 2010 10:39am

I got same problem as  you did, please try following solution, this work on my case

SELECT name, log_reuse_wait_desc FROM sys.databases
--if the log_reuse_wait_desc  is replication then remove it


EXEC sp_removedbreplication YourDatabaseName


SELECT name, log_reuse_wait_desc FROM sys.databases
 

-- log_reuse_wait_desc  on database should be changed to "nothing" now. if yes do following steps

ALTER DATABASE YourDatabaseName SET RECOVERY SIMPLE

 

DBCC SHRINKFILE (N'Your Logical Log Name', 0, TRUNCATEONLY)

 --Now My log file size drop from 18GB to 0MB

 
--after that you should reset you database recovery model to full if you need.

good luck

xyzone


 

May 5th, 2010 11:51pm

I got same problem as  you did, please try following solution, this work on my case

SELECT name, log_reuse_wait_desc FROM sys.databases
--if the log_reuse_wait_desc  is replication then remove it


EXEC sp_removedbreplication YourDatabaseName


SELECT name, log_reuse_wait_desc FROM sys.databases
 

-- log_reuse_wait_desc  on database should be changed to "nothing" now. if yes do following steps

ALTER DATABASE YourDatabaseName SET RECOVERY SIMPLE

 

DBCC SHRINKFILE (N'Your Logical Log Name', 0, TRUNCATEONLY)

 --Now My log file size drop from 18GB to 0MB

 
--after that you should reset you database recovery model to full if you need.

good luck

xyzone


 

Free Windows Admin Tool Kit Click here and download it now
May 5th, 2010 11:51pm

could this problem cause my database backup.bak file to grow large?

Is the database backup trying to backup the transactions in the log file?

March 29th, 2011 8:55pm

I have been looking for days for this answer and finally found it.  The only change I had to make was to set recovery simple BEFORE EXEC sp_removedbreplication YourDatabaseName to get the results you described.  I also had to run DBCC SHRINKFILE on the log file to make it actually shrink.  (DBCC SHRINKFILE ('Audit_Log,2)) 

Free Windows Admin Tool Kit Click here and download it now
December 14th, 2011 6:37pm

The underline issue appears even if you have SIMPLE recovery model in place.

 

Is anyone aware of the actual FIX for this bug? A bug not fixed in 4years, I don’t believe it.

 

In my system, the transaction logs grows to 81gb (free drive space +30gb) and replication just stalls. In addition, replication indicates latency and log reader shows read activities.

The workaround (EXEC sp_repldone) works just fine (though I’ve not checked if any data loss).
December 29th, 2011 10:12am

Hi all,

I have tried the follwoing

EXEC sp_removedbreplication YourDatabaseName


SELECT name, log_reuse_wait_desc FROM sys.databases 
 

-- log_reuse_wait_desc  on database should be changed to "nothing" now. if yes do following steps

ALTER DATABASE YourDatabaseName SET RECOVERY SIMPLE

 

DBCC SHRINKFILE (N'Your Logical Log Name', 0, TRUNCATEONLY)

 --Now My log file size drop from 18GB to 0MB

but  it doen't work to me. Still my log file size is 63 GB. I need to take backup the db and restore it to new location. Since log file is too large, i am not able to restore the bak file in new server.

Can any one help on this ?

Free Windows Admin Tool Kit Click here and download it now
March 29th, 2012 10:05am

Try this

alter database YourDatabaseName set recovery simple

GO

alter database YourDatabaseName set recovery Full

GO

DBCC SHRINKFILE (N'Your Logical Log Name', 0, TRUNCATEONLY

If this does not work do this in your publication database.

dbcc opentran

March 29th, 2012 2:40pm

if replication is running the  execute the below query

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1

then execute

DBCC SHIRINKFILE('DBname_log',0)

as you are using SQL Server 2005 you can use truncateonly to truncate the log file

Free Windows Admin Tool Kit Click here and download it now
January 22nd, 2013 9:59am

THANKS..THIS WORKED
May 22nd, 2015 2:29pm

Hi,

My db server is SQL Server 2012 SP2 and it was the publisher for Snapshot Replication. It's also having the same issue whereby the log could not be truncated due to 'REPLICATION'. In fact, the snapshot replication was already stopped for a while. I've to run sp_removedbreplication to resolve the log issue.

Is this really the Replicate Schema Changes option as I had it turned on previously ? Or is this a bug ?


  • Edited by limssd 4 hours 24 minutes ago Add info
Free Windows Admin Tool Kit Click here and download it now
August 13th, 2015 10:45pm

Hi,

My db server is SQL Server 2012 SP2 and it was the publisher for Snapshot Replication. It's also having the same issue whereby the log could not be truncated due to 'REPLICATION'. In fact, the snapshot replication was already stopped for a while. I've to run sp_removedbreplication to resolve the log issue.

Is this really the Replicate Schema Changes option as I had it turned on previously ? Or is this a bug ?


  • Edited by limssd Friday, August 14, 2015 2:43 AM Add info
August 14th, 2015 2:42am

This issue is relatively rare, so it is unlikely  to be a bug - however you should open up a support incident with Microsoft next time you see this to get to the root of it.
Free Windows Admin Tool Kit Click here and download it now
August 14th, 2015 12:54pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics