Sql-server – how to check who has killed the job which was running ?if logs are not generated

jobssql server

Unspecified error occurred on SQL Server. Connection may have been terminated by the server.

This is error I am getting.(when the job is executed)

[SQLSTATE HY000] (Error 0) Cannot continue the execution because the session is in the kill state.
[SQLSTATE HY000] (Error 596). The step failed.

Best Answer

The error log should contain some information about what session killed a particular process. Run the following statement to get the hostname and process id of the user the KILL command originated from.

EXEC xp_readerrorlog 0, 1, N'kill', NULL, NULL, NULL, N'DESC'

Note that if any time has gone by since the command was run, this may return stale results as session ids are reused. Using the Host Process ID, you can then find the user:

select login_name, session_id, host_name, host_process_id
from sys.dm_exec_sessions where host_process_id = XXXX AND session_id = YY

Here's an example:

EXEC xp_readerrorlog 0, 1, N'kill', NULL, NULL, NULL, N'DESC'

LogDate                 ProcessInfo  Text
----------------------- ------------ ----------------------------------------------------------------------
2017-06-20 07:17:32.210 spid64       Process ID 62 was killed by hostname JOHNLAPPY, host process ID 7968.

(1 row(s) affected)



select login_name, session_id, host_name, host_process_id from sys.dm_exec_sessions where host_process_id = 7968 AND session_id = 62

login_name              session_id host_name  host_process_id
----------------------- ---------- ---------- ---------------
CONTOSO\john.eisbrener  62         JOHNLAPPY  7968

(1 row(s) affected)
Related Question