Post reply

Indexing and TransLog

  • October 18, 2006 at 3:18 pm

    #174876

    I am having weird problem.

    First I tried to DBCC REINDEX on some large table and it runs and stops with Trans Log full. I increased the size of the log and it does it again.

    Second Problem..I backup the log and set the LOG limit to 7000 MB. After a while I run the SP_SPACEUSED and it tells limit is 1500 MB..why it is decreasing even when I have set the limit. FYI It is not set to autogrow.

    How can I complete the REINDEX job without being logs getting filled??

    Dire need of help....Production system needs performance.

    Thanks

    TB


    Tajammal Butt

  • October 19, 2006 at 5:35 am

    #666754

    Did you look in Enterprise Manager,taskpad of the db and see what it shows there ?

  • October 19, 2006 at 6:54 am

    #666768

    This command is your new best friend

    DBCC UPDATEUSAGE (DbName)

     

    Then refresh the data displayed in EM (you MUST manually hit a refresh button for this change to show up in EM).

  • October 19, 2006 at 9:08 am

    #666828

    I do not normally let my databases autogrow. I do, however, set the database to autogrow and set it to a low fixed amount, not percentage. This will get you through any emergencies. Reindexes do use the tranlog heavily and since these are run after hours I do not worry when they cause the tranlog to grow, I would rather have it autogrow then have everything stop. I just shrink the tranlog back to its normal size when I am done. 

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

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