KILL spid never ends (SQL 2000)

Andrew Drake wrote:
> Dear all,
> A developer intended to alter a view in a production database. They issued
> an ALTER VIEW command and, because of some reason, the process hung up.
> In the EM, if I go to Management --> Current Activity --> Process Info I c
an
> see that process as spid 160, the statement is "ALTER VIEW..."
> Any help / ideas / suggestions would be greatly appreciated.
>
Does the view refer to any linked servers? Can you post the query that
makes up the view?Dear all,
From time to time an SQL process ('spid') hangs up in our production SQL
server.
Then we try to kill it using KILL command.
And quite often the KILL never ends.
If I call (spid to kill is 160):
KILL 160 WITH STATUSONLY
the result is:
SPID 160: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
and that result lasts forever (i.e. a couple of days until we restart the
server).
That is quite troublesome as the server is quite heavily used and restarting
it always interferes with normal work of users.
Is there any way to REALLY kill the process?
I guess my problems are related to locking issues, I looked up the
newsgroups but couldn't figure out any feasible solution but restart the
server.
To be more specific, my current problem is as follows:
A developer intended to alter a view in a production database. They issued
an ALTER VIEW command and, because of some reason, the process hung up.
In the EM, if I go to Management --> Current Activity --> Process Info I can
see that process as spid 160, the statement is "ALTER VIEW..."
I killed the process, as described above, but it is still alive.
If I call
select * from sysprocesses where spid = 160
the result is:
spid 160
kpid 1320
blocked 0
waittype 0x0000
waittime 0
lastwaittype OLEDB
waitresource
dbid 17
uid 0
cpu 21265
physical_io 2299
memusage 476
login_time 2006-06-23 08:34:36.450
last_batch 2006-06-23 09:39:55.577
ecid 0
open_tran 2
status runnable
sid 0x010500000000000515000000AA562D4A3E3CC3
6A4B1A1304ED030...0
hostname XXX
program_name MS SQLEM
hostprocess 3900
cmd AWAITING COMMAND
nt_domain XXX
nt_username XXX
net_address XXX
net_library TCP/IP
loginame XXX\XXX
context_info 0x0...0
sql_handle 0x0...0
stmt_start 0
stmt_end 0
Now, if I go to Locks / Process ID I can see that process 160 is marked as
'Blocking'.
Moreover, there are many processes that are marked as 'Blocked by 160'.
The result of
sp_lock '160'
is:
spid dbid ObjId IndId Type Resource Mode Status
-- -- -- -- -- -- -- --
160 17 0 0 DB S GRANT
160 17 0 0 DB S GRANT
160 8 0 0 DB S GRANT
160 8 0 0 DB S GRANT
160 52 0 0 DB S GRANT
160 47 0 0 DB S GRANT
160 17 804197915 0 TAB Sch-S GRANT
160 17 804197915 0 TAB [COMPILE] X GRANT
160 47 1990402260 0 TAB Sch-S GRANT
160 8 985106600 0 TAB Sch-S GRANT
160 17 1767677345 0 TAB Sch-S GRANT
160 52 1893581784 0 TAB Sch-S GRANT
160 8 793105916 0 TAB [COMPILE] X GRANT
160 8 793105916 0 TAB Sch-S GRANT
160 52 1285579618 0 TAB Sch-S GRANT
And that is all what I was able to check.
What can I do in that situation? Is the server restart really necessary?
Probably, I will end up restarting the SQL this time, but I would like to
learn what to do if that happens again.
Any help / ideas / suggestions would be greatly appreciated.
Thank you in advance!
Best regards,
Andrew|||Andrew Drake wrote:
> Dear all,
> A developer intended to alter a view in a production database. They issued
> an ALTER VIEW command and, because of some reason, the process hung up.
> In the EM, if I go to Management --> Current Activity --> Process Info I c
an
> see that process as spid 160, the statement is "ALTER VIEW..."
> Any help / ideas / suggestions would be greatly appreciated.
>
Does the view refer to any linked servers? Can you post the query that
makes up the view?|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> Andrew Drake wrote:
> Does the view refer to any linked servers? Can you post the query that
> makes up the view?
Yes, it does. Actually it is a bit more complicated, because the view refers
to another view in some other database, that in turn refers to a linked
server:
ALTER VIEW DB1.dbo.MyView
AS
SELECT T1.Field1, V2.Field2
FROM DB1.dbo.Table1 T1
JOIN DB2.dbo.View2 V2
ON T1.Field1 = V2.FieldX
where DB2.dbo.View2 refers to a linked server.
Does it matter that a linked server is involved?
Best regards,
Andrew|||Andrew Drake wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> Yes, it does. Actually it is a bit more complicated, because the view refe
rs
> to another view in some other database, that in turn refers to a linked
> server:
> ALTER VIEW DB1.dbo.MyView
> AS
> SELECT T1.Field1, V2.Field2
> FROM DB1.dbo.Table1 T1
> JOIN DB2.dbo.View2 V2
> ON T1.Field1 = V2.FieldX
> where DB2.dbo.View2 refers to a linked server.
> Does it matter that a linked server is involved?
> Best regards,
> Andrew
>
>
Check sysprocesses on the linked server - the process that you killed is
probably waiting on something to finish over on the linked server. In
that case, you'll have to go over to that machine and kill whatever spid
is running over there. Once it dies, then the original spid should die
as well.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> Andrew Drake wrote:
> Does the view refer to any linked servers? Can you post the query that
> makes up the view?
Yes, it does. Actually it is a bit more complicated, because the view refers
to another view in some other database, that in turn refers to a linked
server:
ALTER VIEW DB1.dbo.MyView
AS
SELECT T1.Field1, V2.Field2
FROM DB1.dbo.Table1 T1
JOIN DB2.dbo.View2 V2
ON T1.Field1 = V2.FieldX
where DB2.dbo.View2 refers to a linked server.
Does it matter that a linked server is involved?
Best regards,
Andrew|||Andrew Drake wrote:
> "Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
> news:O89ADPUmGHA.1252@.TK2MSFTNGP02.phx.gbl...
> Yes, it does. Actually it is a bit more complicated, because the view refe
rs
> to another view in some other database, that in turn refers to a linked
> server:
> ALTER VIEW DB1.dbo.MyView
> AS
> SELECT T1.Field1, V2.Field2
> FROM DB1.dbo.Table1 T1
> JOIN DB2.dbo.View2 V2
> ON T1.Field1 = V2.FieldX
> where DB2.dbo.View2 refers to a linked server.
> Does it matter that a linked server is involved?
> Best regards,
> Andrew
>
>
Check sysprocesses on the linked server - the process that you killed is
probably waiting on something to finish over on the linked server. In
that case, you'll have to go over to that machine and kill whatever spid
is running over there. Once it dies, then the original spid should die
as well.|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:ubgQROemGHA.4436@.TK2MSFTNGP05.phx.gbl...
> Andrew Drake wrote:
> Check sysprocesses on the linked server - the process that you killed is
> probably waiting on something to finish over on the linked server. In
> that case, you'll have to go over to that machine and kill whatever spid
> is running over there. Once it dies, then the original spid should die as
> well.
Thank you very much for your help!
Best regards,
Andrew|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:ubgQROemGHA.4436@.TK2MSFTNGP05.phx.gbl...
> Andrew Drake wrote:
> Check sysprocesses on the linked server - the process that you killed is
> probably waiting on something to finish over on the linked server. In
> that case, you'll have to go over to that machine and kill whatever spid
> is running over there. Once it dies, then the original spid should die as
> well.
Thank you very much for your help!
Best regards,
Andrew|||Andrew Drake wrote:
> Thank you very much for your help!
> Best regards,
> Andrew
>
No problem... FYI, the same thing can happen to a process that uses
xp_cmdshell to run an OS command, like a COPY command to copy a large
backup file. Killing the spid will exhibit the same behavior that you
saw, to completely kill it you have to open the Windows Task Manager and
kill the CMD.EXE process that was spawned by xp_cmdshell.