Post reply

Log growing Unpredictably

  • January 29, 2010 at 4:03 am

    #98979

    Hi All,

    I have a problem with one of my database; while updating a table contains oddly 50,000 rows, the log has increased from 4MB to 14GB and the update is not completed.

    I have tried the below options but veined.

    DBCC CHECKDB

    DBCC CHECALLOC

    DBCC CHECTABLE

    DBCC REINDEX

    Could any one help with the cause and the resolution?

    Thanks in Advance.

    😛

  • January 29, 2010 at 4:12 am

    #1110612

    Are there any uncommitted transactions? Check the DBCC OPENTRAN topic in SQL Server Books Online.

    Facts are stubborn things, but statistics are more pliable - Mark Twain
    Carolyn
    SQLServerSpecialists[/url]

  • January 29, 2010 at 4:17 am

    #1110614

    I tried with DBCC OPENTRAN, it shows nothing...displays below msg

    No active open transactions.

  • January 29, 2010 at 5:24 am

    #1110641

    You're updating 50 000 rows in a single transaction? That's gonna cause the log to grow. The log will have to be big enough to store the before and after versions of those rows that you're updating plus all the extra data required in a log record.

    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

  • February 1, 2010 at 11:31 pm

    #1112095

    Thanks for your suggestions...

    I have looked around the problem w.r.t triggers, and i found that, the trigger is causing the problem, i have two triggers one is for update and another is for insert. both r checking a field say 'X', if it is updated then the value is inserting along with the Identity column 'I' in one History table. the history table consists of 1.5K records. while i am running the update statement on the base table, it is holding the exclusive lock on both history and base tables.

    at this point, i found the log is growing unexpected. those are simple insert statements in the triggers, i doesn't have clue, why it is happening.

    Suggest me with the resolution...

    Thanks,

Viewing 5 posts - 1 through 4 (of 4 total)

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