Can''t truncate log becuase of pending replication

  • Hi all,

    SQL2000 (SP3) running on Windows 2003.

    The transaction log of our reporting database has grown to over 10gb and needs to be truncated.

    This database is updated each night from a non-Windows system and therefore has a 'Simple' recovery model.

    This database is replicated out to customer specific satellite databases using Snapshot replication

    We need to reduce the size of the transaction log because backups are taking too long and we are also running out of space.

    Tried to run the following command to truncate the log

    backup log [mydb] with no_log

    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.

    We really dont care about the records in the log as each day the subscribing databases are reloaded fully using snapshot replication.

    The is no 'Log Reader Agent' in the Replication folder???

    Can anyone tell me how I can truncate this log on a daily basis?

    I've looked into sp_repldone but this looks more like an emergency solution as it would need publishing to dropped/enabled.

    Thanks in advance

    Dave

  • use DBCC sqlperf(logspace) to see how much your log file is used.

    then use backup log dbname with truncate_only

    then dbcc shrinkfile(logfilename,targetsize)

    where traget size should be more than the size being used currently.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • Hi,

    DBCC sqlperf(logspace) returns...

    Database Logsize Log used %

    XPS-2-SQL10667.6893.100380

    backup log [xps-2-sql] with truncate_only

    returns the same as doing with no_log (I thought no_log and truncate_only are synomymous)

    I planned to do a shrinkdatabase when I've got the log size down a bit.

    Dave.

  • Do a checkpoint in your database so that all the uncommited transaction commit then you will have enough space to truncate and allocate to OS.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • Was this database acting as a publisher in a transactional replication before snapshot replication was set up? If so, you may need to perform some 'cleaning up' in order to remove the 'replicated flags' in the transaction log, which is what is causing you some angst.

    Paul

  • Not really sure although there's a good chance that is has been used to

    'test' out different types of replication. Before this current setup went live

    replication disabled/enabled. The transaction log was not truncated though.

    Would it be worth dropping the transaction log and re-creating a new

    one? Or would I still have the tidying up to do?

    Dave.

  • No sure if I'm following. There are no uncommited transactions in this database as

    it's updated three times each evening in the small hours.

    I can certainly do a checkpoint command but what will this solve?

    Dave

  • use [xps-2-sql]

    go

    checkpoint

    backup log [xps-2-sql] with truncate_only

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

  • When you are *certain* about the way the database is being used, you can do the following:

    1. execute sp_removedbreplication '<database name>' at the publisher;

    2. try your shrink strategy again;

    3. if (2) does not work, then you can use sp_detach_db to detach the database, delete the log file, then use sp_attach_db and supplying all but the log file path.

    Paul

  • Hi,

    Replication is definately snapshot but the way it's used is slightly

    non standard. Basically all the SQL agent jobs created by the replication

    setup procedure are scripted and merged into a single job.

    Because of this I would like to avoid dropping and re-creating replication

    if at all possible.

    Your point #3 sounds interesting. Surely this will effectively get rid of the bloated

    and unrequired logfile and allow me to create a new one??? Would this method allow me

    to do the following as part of a daily scehduled job in the future?

    a) truncate the log

    b) dbcc shrinkdatabase

    Dave

  • When you reattach a database without specifying the log file, SQL Server will create a new log file for you.

    If you take this route, there should not be an issue with the log file as you are experiencing at this moment, because only transactional replication makes extensive use of the log file. HTH.

    Paul

  • Hi,

    Can't detach the database because replication is enabled.

    Looks like I may have to completely remove replication and then re-create a new log file.

    Can't see any other way around this.

    Dave

  • You can always script the replication setup, which you should have done already for disaster recovery.

    Do try and test the detach/attach, etc on a restored copy of the database if that is possible. When you are confident then try it out on the production system during your maintenance window.

    Paul

  • issue an sp_repldone and then do the backup/truncate

  • If you use sp_repldone you should be aware of the consequeces !!!!


    * Noel

Viewing 15 posts - 1 through 15 (of 19 total)

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