Who Killed My SQL Server process – session – connection – spid?
If you are sharing single database server with multiple users you might many times end up your process being force killed and an error message shown below comes up.
Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.)
You might be interested in policing as to who killed your process?
Was it done intentionally or some server side error occurred?
In this post let us analyze a technique to sport the user who killed your process:
Using SQL Server error Log:
SQL Server logs errors to the sql server error log, It can be found that it also logs the killing of processes in its error log.
So first query the latest error log for the word kill.
[sql]
EXEC sys.xp_readerrorlog 0,1,’kill’
[/sql]
If you cannot find any results then continue with the next error log
[sql]
EXEC sys.xp_readerrorlog 1,1,’kill’
[/sql]
You can find the logdate,the killed SPID ,Host name and host processid that killed the process in the result
Now find out the record for your processid in the result.
To find the user who killed your process run the below queries replacing the host process id with the value that you found out in the above step.
[sql]
select login_name,* from sys.dm_exec_sessions where host_process_id = ‘[host process ID]’
[/sql]
The login_name in the result gives you the login name of the user who killed your process.
Hope this post helps..
Applicable to sql server 2005,sql server 2008,sql server 2008 r2,sql server 2012
Also read: Read SQL Server Error Log Using TSQL Query
Do Comment if you wanted to discuss further or have any issues.