Reindexing failing

Question

Reindexing failing

Bhaskar Bhatt

SSC Rookie

Points: 41
More actions
April 8, 2005 at 2:15 am

#92800

I have scheduled the jobs for reindexing particular databases . the exact statement looks like
---------------------
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?')"
EXEC sp_MSforeachtable @command1="exec sp_recompile '?'"
------------------------------------
This used to run fine but since last few days this fails . when this job runs it blocks all the proceses . I don't have a clue why this is happening . These are live biling servers and this causes the services to litrally stop.
Any suggestions are welcome ,
thanks,
Bhaskar

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

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

Martin Mojzisek SSCrazy Points: 2207 More actions · Answer 1

DBCC DBREINDEX is not an on-line operation - table being processed is not accessible to other processes. I've seen assertions that this is true only when clustered indexes are rebuilt, but when tested for myself, even if you rebuild nonclustered indexes, the table cannot be used by other users.

Read this whitepaper for more info:

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

also check DBCC DBREINDEX and DBCC INDEXDEFRAG in Books Online

Doug Blystone Ten Centuries Points: 1144 More actions · Answer 2

My experience is simliar. However, did you see a sudden increase in the time to complete the job? Can you space out your reindexes by changing fill factors? My experience with DBCC INDEXDEFRAG though an online operation takes a lot longer in a lot of cases. (probably becuase I still had usage on the tables in question).

Reveiw your job logs to ensure the time to completion is the same.

Bhaskar Bhatt SSC Rookie Points: 41 More actions · Answer 3

Ya the increase is there . last time it used to run for 4 min but this time it kept on running for around 6 hrs and brought down the systems by blocking everything . I killed the job and everything was fine . Then i tried to run the same again after defragmentin the indexes on the major tables but it did not work... am still looking for answers.

Bhaskar Bhatt SSC Rookie Points: 41 More actions · Answer 4

I ran the DBCC SHOWCONTIG but want to know what value is the indication that things are not OK I see the following values - PagesScanned ,Extents Scanned, Extent Switches,Avg. Pages per Extent, Scan Density [Best Count:Actual Count], Extent Scan Fragmentation ,Avg. Bytes Free per Page, Avg. Page Density (full).

Now can somebody elaborate which one is can show that things are not ok ? and what should be done .

Thanks,

Bhaskar

DBAEngg Old Hand Points: 394 More actions · Answer 5

As Martin said, DBCC DBREINDEX is not online operation as that exclusively locks your table.

On the other hand, DBCC INDEXDEFRAG is online operation but it has it's own limitations.

now answer to you question is scan density is the correct measure to check the index fragmentation if your indexes are not spread across multiple files. Higher values towards 100 are best value 100 shows no fragmentation.

now you can selectively build index depending on fragmentation

I hope following link will help you.

http://www.sql-server-performance.com/tp_automatic_reindexing.asp

Thanks.