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

    Question

  • 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
    Thursday, March 6, 2008 8:59 PM

Answers

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

    Friday, March 7, 2008 2:44 AM
    Moderator

All replies

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

    Friday, March 7, 2008 2:44 AM
    Moderator
  • 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
    Friday, March 7, 2008 3:59 PM
  • Hi i have the same problem and i don´t 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.

     

    Tuesday, April 29, 2008 12:42 PM
  • 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
    Tuesday, April 29, 2008 2:05 PM
  • I have the same problem and I tried to run EXEC sp_repldone @xactid=NULL,@xact_segno=NULL,@numtrans=0,@time=0,@reset=1 but it says database is not published.

     

    Wednesday, May 21, 2008 12:57 PM
  • 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.

    Thursday, July 24, 2008 6:32 PM

  • Has this been identified as a problem within the replication framework and if so, apart from the workaround suggested in prev posts, is a resolution available ? 
     
    Friday, March 27, 2009 7:58 PM
  • 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.
    Best regards, Dietmar.
    Thursday, December 31, 2009 2:22 PM
  • 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
    • Proposed as answer by Jeff Burinda Friday, November 20, 2015 8:35 PM
    Thursday, December 31, 2009 3:06 PM
  • 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?

    Steve LaRochelle
    Thursday, January 7, 2010 7:44 PM
  • 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
    Thursday, February 11, 2010 10:39 AM
  • 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


     

    Wednesday, May 5, 2010 11:51 PM
  • 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?

    Tuesday, March 29, 2011 8:55 PM
  • 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)) 

    Wednesday, December 14, 2011 6:37 PM
  • 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).
    Thursday, December 29, 2011 10:12 AM
  • 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 ?


    Thanks, Karikalan N

    Thursday, March 29, 2012 10:05 AM
  • 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


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Thursday, March 29, 2012 2:40 PM
    Moderator
  • 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


    Ramesh Babu Vavilla MCTS,MSBI


    Tuesday, January 22, 2013 9:59 AM
  • THANKS..THIS WORKED
    Friday, May 22, 2015 2:29 PM
  • 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
    Friday, August 14, 2015 2:42 AM
  • 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.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Friday, August 14, 2015 4:51 PM
    Moderator
  • Thank you for posting this.  Placing the db in "simple" mode allowed us to remove the replication with an above command and shrink the TLOG.
    Wednesday, March 28, 2018 3:00 PM