• I have a problem I can't shrink the transaction log file

    is now 11GB and Datafile is 10GB

    I look into column from sys.database LOG_REUSE_WAIT_DESC

    it say "REPLICATION"

    But I can't delete the replication from management studio.what can i go now ?

    Mittwoch, 14. März 2012 08:36


Alle Antworten

  • It sounds as though the database that you are unable to shrink the transaction log for is also a Publisher in a Replication Topology.

    If the transactions have not made it to the Ditributor successfully then they will remain in the Publisher transaction log. If dropping the Publication is acceptable then you should be able to do so by reviewing your Replication topology in SSMS, whilst connected to the instance hosting the database with the issue, expand  the replication folder and then Publishers, where you should see the Publication for the relevant database listed. From here it can be dropped.

    John Sansom | SQL Server DBA Blog | @JohnSansom on Twitter

    Mittwoch, 14. März 2012 08:40
  • I can't delete the Publication. there is a error to ask you to see the LOG_REUSE_WAIT_DESC sys.database again.
    Mittwoch, 14. März 2012 08:50
  • Are you able to drop all articles from the Publication? This will remove pending replication transactions from the Distribution db?

    John Sansom | SQL Server DBA Blog | @JohnSansom on Twitter

    • Bearbeitet John Sansom Mittwoch, 14. März 2012 09:48
    Mittwoch, 14. März 2012 09:01
  • No ~ I am not able to drop.
    Mittwoch, 14. März 2012 09:03
    • Als Antwort vorgeschlagen John Sansom Donnerstag, 22. November 2012 21:34
    Mittwoch, 14. März 2012 09:51
  • what is the error message that you get when you try to drop?

    Regards, Ashwin Menon My Blog - http:\\

    Mittwoch, 14. März 2012 10:29
  • I had the same problem and also could not drop the publication because it gave the same error about transaction log full.  In my case, this was a onetime use snapshot publication, so I was surprised when it caused the log to fill up due to non-distributed transactions.  I was able to use the following to fix the problem.
    USE ProblemDB
    EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
    EXEC master.dbo.sp_removedbreplication
    	@dbname = 'ProblemDB'
    CAUTION: that use of sp_repldone is telling the database to IGNORE any undistributed transactions and pretend like they were actually distributed.  That may not be acceptable in your case.   For me it did not matter because I did not care about those transactions.

    I went ahead and dropped the publication, since I no longer needed it, but you may not need to do so.  Then I ran the checkpoint to let the transaction log know that the transactions had been dealt with, so that I could then shrink the log back to a reasonable size.

    I hope my painful experience and the extra digging I had to do to solve it make it easier for someone else to reslove it when this issue comes up.


    • Als Antwort vorgeschlagen BillNye Donnerstag, 22. November 2012 21:05
    Donnerstag, 22. November 2012 21:04
  • You did not by chance have the Replicate Schema Changes property enabled did you?

    John Sansom | SQL Server DBA Blog | @SQLBrit on Twitter

    Donnerstag, 22. November 2012 21:34
  • Hi,

    In My case, column log_reuse_wait_desc returned REPLICATION (e.g. BOL Factors That Can Delay Log Truncation). So the log was not truncated because records at the beginning of the log are pending replication.

    Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed. Typically the Log Reader agent will parse the entire log and then mark each log record as replicated by executing sp_repldone.

    Thanks and Regards


    Donnerstag, 22. November 2012 23:57
  • You did not by chance have the Replicate Schema Changes property enabled did you?


    Yes, the Replicate Schema Changes property subscription option was set to True.  Apparently that is the default since I did not intentionally set that.  Besides, this was a one-time snapshot, so I wouldn't have been concerned with that setting.  Are you saying that would have been a factor?  I wouldn't have thought so, especially since there weren't any schema changes that I can recall.


    My case was the same as yours, except I wasn't expecting that the log reader agent would be running or that I would have to do anything with sp_repldone because it was only a snapshot publication.

    In any case, what I posted previously resolved the problem.  I just thought it was odd that a snapshot publication kept tracking transactions to be replicated.  At the time, I just needed an answer fast on how to fix it.



    Mittwoch, 28. November 2012 00:42
  • Bill,

    There reason I ask is because there have been a number of bugs surrounding Truncation of the Transaction Log for Snapshot Replication, for example: FIX: Transaction Log of Snapshot Published Database Cannot Be Truncated

    In a recent case, I encountered an issue whereby the Replicate Schema Changes setting was the source of the issue. As soon as we set this property to false, we were able to successfully clear down the log. Like yourself, we did not require the ability to replicate schema changes and so had no concerns with adjusting the Publication to reflect this.

    John Sansom | SQL Server DBA Blog | @SQLBrit on Twitter

    Mittwoch, 28. November 2012 08:40