question

AZAD_MARATHA avatar image
AZAD_MARATHA asked

SPID STATUS SUSPENDED

i created a maintanence plan for rebuilding index. but today one of my tables index got blocked.i mean the spid to rebuild that index got suspended.becoz of that my application was giving constant errors whenever it tried to access that table. so (a) query got a request timeout and (b) succeeded

a.select * from table. b.select * from table with nolocks.

i tried lot of things but i failed

  1. drop index
  2. disable index
  3. delete index
  4. rebuild and reorganise

then ultimately i killed that process.and bingo it worked.

it was normal process to rebuild index and command field was ALTER INDEX

my question is

HAVE I DONE THE RIGHT THING ? WAS THERE ANY OTHER SOLUTION ? I CHECKED THE LOG FILES OF THAT MAINTAINENCE PLAN.AND IT DIDNT SHOWED ME ANY ERROR. WHY?

sql-server-2005deadlockblocking
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered

The thing to do would have been to check sys.dm_exec_requests and see what the process was waiting on. If you knew what was causing it to hang, you might have been able to address that directly rather than kill the process.

However, killing the process was a better joice than any of the other operations you tried.

10 |1200 characters needed characters left characters exceeded

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

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.