sa spid - need to kill - server hanging

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Glenn - 25 Aug 2006 06:23 GMT

Hi

I attempted to add a column to a table containing around 10 million
rows (will do it differently next time :-) and ever since the server
has been hanging and only occassionally can you connect to it and do
anything with it. It all seems to be hinging on a process that is
running as sa user I wasn't logge in as sa though), which I can't kill.
Restarting the SQLServer service and even rebooting the box has no
effect - the process is still there and continues to hog around a gig
of ram and counting and performing endless disk io.

I can't intervene in anyway, it seems. I tried to drop the table that
it all started from, but I cancelled this operation after about 30
minutes. The server has been in this state for around 3 hours now and
not sure how to resolve. The database is dispensible - I can simply
restore from back up so would be happy to drop it but this seems
impossible.

Any suggestions how to stop this rogue process.
Using SQLServer 2000, latest SP.

Glenn

Reply to this Message

DallasBlue - 25 Aug 2006 15:25 GMT

I would shut down the service / server. If am not able to connect at all. I
dont have a sane answer at this time.

> Hi
>
[quoted text clipped - 18 lines]
>
> Glenn

Reply to this Message

Arnie Rowland - 25 Aug 2006 17:46 GMT

Sounds like adding a column using Enterprise Manager instead of Query
Analyzer...

When you stop the server service, the 'rogue' process will be aborted.
However, when you start the server back up, it may take quite some time for
the last incomplete transaction (the rogue process) to be rolled back and
the server back online. Perhaps that is why it 'seems' that the same rogue
process is back.

Signature

Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

> Hi
>
[quoted text clipped - 18 lines]
>
> Glenn

Reply to this Message

Glenn - 27 Aug 2006 23:07 GMT

Thanks Arnie, and other responders.

Yes, it seems the system was rolling back the process (after I aborted
it by stopping the SQL Server service), it was just using a LOT of
resources and time to do the rollback! I guess it uses the sa user so
that the rollback process can not be killed (therefore enforcing data
itegrity). When I looked at it after the weekend, the process had
finally ended, the db was in the state it was in prior to attempting to
add the columns, and essentially all is good.

Thanks all

Glenn

Reply to this Message