October 29, 2002 at 11:52 am
Hello everyone, I did a simple query and found out that this process has been running for over 200 hours. I ran dbcc inputbuffer(spid) and got the following:
Event Type Parameters EventInfo
Language Event 0 EXECUTE msdb.dbo.sp_help_alert @order_by = N'severity ASC, message_id ASC, database_name DESC'
sp_who says that the status of that spid is Sleeping, the database it is in is msdb, and it is awaiting command.
Is this an orphaned process?
October 29, 2002 at 3:54 pm
I think that the server use it for internal functions. There's nothing you can do with them. I guess that you can't kill them either.
November 1, 2002 at 2:55 pm
What makes you think its been running for 200 hours?
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 1, 2002 at 5:29 pm
Well, its actually more like a process was run 200 hours before and was still running. I queried sysprocesses.last_batch and the last batch that was run was run 200 hours before and it was obviously still running (or at least thats what I think). My query showed me the spid and dbcc inputbuffer() showed me what is posted in the original message.
Why would a spid hang around for that long?
November 2, 2002 at 9:13 am
THe last batch refers to the last time a query was SQL batch was pushed thru that connection. Does not mean it has been running that long. If the state is sleeping then it stopped at some point but unfortunately MS does not tell run time of batch.
Now as for how a SPID can hang, it can be multiple ways. Bad driver install at the client, bad coding in the app not closing the connection properly, connection held open for connection pooling (however unusually would remain that long), clinet lost network connectivity and connection did not tear down (usually SQL will clean these up itself unless a router somewhere is reporting the connection still valid and open, this is rare on newer equipment but have seen on several occassion on older equipment). It should tell you the originating clients name, if you have a good network topograph and they keep up with host names you may be able to find them. Or try a net send message to them to get them to call you. Other than that KILL will destroy it, just make sure it's value is not a system SPID.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply