Post reply

Transaction Log Growing, cant truncate.

  • March 7, 2007 at 8:43 am

    #172626

    My transaction log is over 4gb.

    data file is 1gb, but only using 30% or so.

    I am performing full backups weekly, differential daily and transaction log every 10 minutes.

    dbcc sqlperf(logspace) indicates 91% used.

    DBCC

    LOGINFO returns 227 rows. I know this way too high, but I am not sure at the moment why and how to correct this?

    The status of 220 of the rows is 2 with the remaining 7 rows with a status of zero at the end.

    Thoughts?

    Thanks.

    After more reading I have determined that I have open transactions in the log file which of course still have a status of 2.

    Is there any way of deleting these open transactions?

  • March 7, 2007 at 11:20 am

    #693621

    To truncate a file,

    DBCC SHRINKFILE (FL_W20060730, EMPTYFILE)

    DBCC SHRINKFILE (FL_W20060730, TRUNCATEONLY)

    to truncate inactive logs,

    BACKUP LOG dbName WITH TRUNCATE_ONLY

    Are they helpful?

  • March 7, 2007 at 11:24 am

    #693625

    Thanks for your reply.

    Turns out it was due to replication. There was old transactions for replication in the tran log waiting to be committed to the subscriber. Since the server is not longer a publisher, it will never clean out, I guess.

    I executed sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,@time = 0, @reset = 1,

    which cleared out the uncommited transactions, then ran shrink.

    thanks for your input.

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

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