SQL Server Performance

Transaction Log problem

Discussion in 'Performance Tuning for SQL Server Replication' started by Jon M, Jan 11, 2005.

  1. Jon M Member

    I restored a SQL server 2000 (SP3) database that is being replicated from a new sql server instance. Replication was broken from this new instance and the database is currently in full recovery model. I was trying to backup the log using:

    backup log [mydatabase] with truncate_only

    But I am getting below error:
    "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."

    I've done everything to manually remove any replication info such as:
    EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

    sp_removedbreplication '[mydatabase]' and other system sp. Any idea on how can I get rid of the transaction log? I am thinking of shifting from full to simple, create a full backup of my database and delete the transaction log afterwards. Is this a good idea?

    Jon M
  2. thomas New Member

    you could try detaching the database then re-attach it but use sp_attach_single_file_db to get SQL Server to create a nice new replication-free log.

    Tom Pullen
    DBA, Oxfam GB
  3. satya Moderator

    Are you using 'Sync with backup' option on your replication databases?
    You can deploy what Tom referred and if you're deploying SIMPLE recovery model then ensure full database backup are scheduled frequently.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  4. Jon M Member

    Thanks. I detached/re-attached and it worked.

    Jon M
  5. bj007 New Member

    Do you think detaching attaching this way, will work fine, even if I have multiple datafiles. Will specifying the first MDF file enough ?
  6. satya Moderator

    You must specify all qualifying files such as .MDF & .NDF in order to reattch the database.
    But in this case check if you're using 'Sync with backup option' then try to avoid the backup clash between replication process. Ensure transaction log is sized with proper settings.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page