Monday, February 20, 2012

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.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.|||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.

No comments:

Post a Comment