none
Replication Failing, because log file is full RRS feed

  • Question

  • I am having a problem with replication between 2 databases.  Here is some context on the setup.

    I have 2 database servers, which are both running SQL Server 2005.  Each database server contains a database called CRIS_TEST_ARCHIVE, these databases are setup as replication subscriber and publisher and replication has been running flawlessly so far.

    As part of a project, we are archiving a lot of data, ie, removing it from the CRIS_TEST_ARCHIVE database and putting it into a different database.  the export went fine, and I am partway through the step of deleting the redundant data from the current database.  This involves deleting a lot of rows from the source database and then running replication to ensure that the deleted rows also get deleted from the other replicated database.

    I am running the deletes for each table one by one, and then running replication manually.

    After one particularly large delete (about 9 million rows) i again started replication and got the following error in the replication manager:

    ----------------------------------------

    Error messages:
    The Merge Agent was unable to update information about the last synchronization at the Subscriber. Ensure that the subscription exists at the Subscriber, and restart the Merge Agent.  (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199481)


    Get help: http://help/MSSQL_REPL-2147199481
    The transaction log for database 'CRIS_TEST_ARCHIVE' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases (Source: MSSQLServer, Error number: 9002)
    Get help: http://help/9002

    ----------------------------------------

    OK, so the log file is full.  I did some research on this topic and determined several ways to shrink it, by researching these topics: 

    http://msdn.microsoft.com/en-us/library/ms179478.aspx

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/3c901c06-05cc-48d6-9397-8b2438efd1e1

    So far, i have tried the following:

    -run a full backup of the database

    -backup the transaction log for the database (twice)

    -use dbcc shrinkdatabase

    -use dbcc shinkfile on the log file

    run the following statements in SQL query manager:

    USE DatabaseName

    GO

    DBCC SHRINKFILE(<TransactionLogName>, 1)

    BACKUP LOG <DatabaseName> WITH TRUNCATE_ONLY

    DBCC SHRINKFILE(<TransactionLogName>, 1)

    GO

    --------------------------------------

    while my log file is now smaller (160Mb as opposed to previously, over 40GB), still, the replication is failing with the same error.  Can anyone advise on how to get replication working?

    Monday, September 20, 2010 7:29 PM

Answers

  • nevermind, i figured it out!  it was the DESTINATION database log file that was filling up, the error was referring to that, not the source database.  once i shrunk the log files on the destination db, replication started working again.
    Monday, September 20, 2010 8:25 PM