2

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.

  • And why are logs not generated? – George K Jun 20 '17 at 12:37
  • Particular error message "Cannot continue the execution because the session is in the kill state", has severity 21 which is enough to stop it. If possible please post more error log information. – CR241 Jun 20 '17 at 15:04
4

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)
2

I suggest to check errorlog for the time when the error occured. It could be not "kill", for example I saw the same error when DBCC CHECKDB was executing, the job was failed with the same error

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

It was a session that put the database in single_user with rollback immediate The errorlog has no killing message but it did reported the spid that put the db in single user.

To find a login who did it I searched in the default trace by the execution time as well, there was a row reporting EventClass = 164 and ObjectType = 16964 that corresponds to alter database, DatabaseName reported was the database of interest and SessionLoginName was that I was looking for

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.