Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2005

    Unanswered: Please Help!...Hanging DBREINDEX Job

    I have a scheduled job on my SQL Server 2000 that runs a DBREINDEX job on all the tables in my database. For some reason the system just hangs for hours now when it reaches one particular table. Can somebody please help me to correct the problem. Thank you all in advance.

  2. #2
    Join Date
    Nov 2004
    on the wrong server
    Provided Answers: 6
    how do you know it is hanging. How large is the table and how big are the indices on the table? Sometimes this can take a long time on big tables.

    you may consider putting your database in bulk-logged recovery or changing your approach.

    on large databases it is often impractical and not necessary to expect to reindex the whole database at once. a more refined approach is to check for the fragmentation of the indexes and to reindex the individual tables when only necessary, say when fragmentation reaches 10 to 15% (DBCC SHOWCONTIG in SQL2K).

    you may also want to change your approach by using drop and create index on the clustered index on the table because if you drop and recreate the clustered the nonclustered ones are automatically recreated.

    and I forget whether the tempDB has any roll in this operation but you may want to monitor it's growth and make sure it is not auto growing and that it has enough room during this operation.

    this may be my most substantial post in months.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jun 2003
    Provided Answers: 1
    I've had this happen occasionally if my script is trying to reindex a system table (which may be used in the reindexing process...), or it tries to reindex a table where I was storing output or results of the reindexing job itself.
    Strangely, the issue only occurred occasionally.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts