can't kill process!

Strange thing. I have a spid that says the process is
"select * from user-table1". The spid is in "sleeping"
status. I can't kill the process and I have tried 20
times! It won't go away. I have stopped and started the
Sql Server services. I have rebooted the Sql Server box.
Its always there and it is causing probelms with my DB
Maint jobs. How can I get rid of the spid if Kill does
not work?
Thanks,
Brianbriang (anonymous@.discussions.microsoft.com) writes:
> Strange thing. I have a spid that says the process is
> "select * from user-table1". The spid is in "sleeping"
> status. I can't kill the process and I have tried 20
> times! It won't go away. I have stopped and started the
> Sql Server services. I have rebooted the Sql Server box.
> Its always there and it is causing probelms with my DB
> Maint jobs. How can I get rid of the spid if Kill does
> not work?
If you stop and start SQL Server, and the process is still there,
this indicates that the client program keeps reconnecting and
submits the query over and over again. This should be visible
in sysprocesses by the column last_batch always being a recent
point in time.
So you would somehow have to find that client and see what it is up to.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Run sp_who2 from Query Analyser. Located the spid in question and retrieve
the HostName, Login and ProgramName from the results. This will give you vi
tal clues as to what the culprit is. eg A service account on a particular wo
rkstation or server running
say a .NET application or component. If you can't locate the "application"
in question take this information to one of your network administrators (who
should be able to solve this one for you).