Post reply

Cannot truncate transaction logs with snapshot replication enabled

  • June 3, 2009 at 3:14 am

    #132925

    I am currently experiencing significant difficulties truncating transaction logs on a database which is being replicated to another sql server. The database is running in a fresh install of SQL2005 64bit and has no service packs installed. The problem does not occur if I remove all replication components and truncate the transaction log.

    The database is relatively small about 5GB size in total and is configured with the full recovery option. When Snapshot replication is not enabled I can perform the following steps without any problems:

    SQL

    1)dbcc opentran - Reports no open transactions

    2)backup log 'database name' with truncate_only - Completes successfully

    3)Shrinkfile ('log name', 'new size') - Completes successfully

    Once I enable Snapshot replication and configure the publication I can no longer truncate the transaction log or shrink the log file. The log file continues to grow and eventfully fills up the physical drive. A full backup of the database is made each night but whenever I run dbcc loginfo (database name) a number of the transactions remain with a status of '2'. If I run dbcc opentran it reports a non zero oldest non-distributed LSN. When I run a select 'all' statement against sys.databases view, it returns a value of 'replication' in the log_resuse_wait_desc. I was wondering if anyone could provide me with any pointers for resolving this issue.

    Thanks

    Mark

  • June 3, 2009 at 5:11 am

    #1003894

    Hey

    There are a few tip on the link below

    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

  • June 4, 2009 at 5:34 am

    #1004531

    Thanks, that was the workaround I was looking.

  • June 4, 2009 at 6:06 am

    #1004555

    thats becaue sp_repldone will update the system tables and mark all the transactions as complete ...this will change the VLF status to 0 ..

    Abhay Chaudhary
    Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply