question

Andomar avatar image
Andomar asked

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?

maintenanceblocking
10 |1200

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

Andomar avatar image
Andomar answered

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

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

Steinar avatar image
Steinar answered

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 comment
10 |1200

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

Andomar avatar image Andomar commented ·
Thanks for the pointers to sp_who2 and sp_locks! The root cause was a second process
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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