Post reply

??? SQL Server 2000 Transaction log Growing to 180 GB cannot truncate ???

  • January 15, 2008 at 9:48 pm

    #76867

    Hi

    Transaction log of our user database has grown up to 180 GB and now we are not able to truncate the log file.

    We have tried BACKUP LOG DBName TRUNCATE ONLY, DBCC SHRINKDATABASE

    Error we get is "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."

    So we then Backed up the FULL database from enterprise manager and then tried to truncate log files using bothe the commands but no luck

    even after backing up Transaction log from enterprise manager we are not able to truncate the log files.

    any help on than highly appreciated

    Regards,

    Forforums

  • January 16, 2008 at 12:44 am

    #768481

    The error message pretty much says it all

    Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed."

    You have transactional (or perhaps merge) replication running and, I'm guessing, the log reader agent has failed. The oldes entries in your transaction log (the ones you want to truncate) are marked for replication but not replicated.

    those entries can't be discarded from the log untill they have been marked as replicated.

    Check your log reader, either get it running or remove replication, then back up your log (not truncate)

    You should then be able to shrink the transaction log file down to a reasonable size. Use DBCC shrink file to just shrink the log file. Shrinking the full database has nasty effects on index fragmentation.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 21, 2008 at 9:38 pm

    #770331

    thanks

    all that advice helped and database is now running fine

  • April 1, 2008 at 12:40 pm

    #796795

    I am having the same problem but only the database is not replicated. (This is a database that was copied from a different server which was replicated).

    On the new server (not replicated) the txn log can't be truncated because it thinks it is replicated. It allowed me to disable the replication on the new server but still i am having issues. Any help would be appreciated. Thanks.

    Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.

  • April 1, 2008 at 12:45 pm

    #796797

    The only way I found was to setup replication and replicate the DB then use the sp_repldone. Then I removed replication and all was well. Probably could look at sp_repldone to figure out what it actually does but cleanup usually is better if you do it the right way.

  • April 1, 2008 at 12:46 pm

    #796799

    Antares686 (4/1/2008)

    The only way I found was to setup replication and replicate the DB then use the sp_repldone. Then I removed replication and all was well. Probably could look at sp_repldone to figure out what it actually does but cleanup usually is better if you do it the right way.

    Scratch lookign at sp_repldone, it is an extended sp.

  • April 1, 2008 at 1:13 pm

    #796815

    Thanks. That helped.

    I enabled the replication and then did a sp_repldone and then disabled the replication. Then I was able to truncate the log and shrink the file. Thanks for your help.

    Give me a fish, you feed me for a day! Teach me to fish, you feed me for a life time.

Viewing 7 posts - 1 through 6 (of 6 total)

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