x

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?

more ▼

asked Jan 28, 2010 at 11:31 AM in Default

AZAD_MARATHA gravatar image

AZAD_MARATHA
33 2 2 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered Jan 28, 2010 at 11:49 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
98.9k 19 21 74

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1945
x39
x24

asked: Jan 28, 2010 at 11:31 AM

Seen: 4058 times

Last Updated: Jan 28, 2010 at 11:31 AM