Hanging reindex blocks other processes

Question

question

Andomar asked Jan 12, '10

Hanging reindex blocks other processes

This sunday, our SQL Server 2005 was running a scheduled maintenance. One of the reindexes did not start; it was showing in the job list as Suspended, waiting on a LCK_M_SCH lock. We take that to mean it's waiting for a modify schema lock, so the reindex never actually started. Would you agree with this analysis?

The task was blocking a lot of other processes, but the "blocked by" for the reindex process was empty. When we killed the reindex process, all other tasks proceeded normally. What could the reindex have been waiting for?

blocking maintenance

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-01-13T16:08:09Z

Andomar answered Jan 13, '10

The cause was a process that was hanging while reading a view. The view was using with (nolock), so it did not block other reads or writes. But it did block an ALTER TABLE statement. In turn, the ALTER TABLE statement blocked other reads and writes. Killing the ALTER TABLE solved the problem, which kind of hid the root cause.

Once we killed the hung process, ALTERs worked fine again.

The hung process was not shown as blocking the ALTER process.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-01-12T14:15:16Z

Steinar answered Jan 12, '10

It could have started but then stalled during the job.

It's hard to say what it was waiting for if "blocked by" was empty, did you save any output from sp_who2 of sp_locks?

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Andomar commented · Jan 13, 2010 at 04:08 PM

Thanks for the pointers to sp_who2 and sp_locks! The root cause was a second process

0 ·

question