SQL SERVER – Process ID X was killed by hostname ABC, host process ID Y

Errorlogs are an awesome place to learn something new every time. It springs up with some new errors which you might have not seen ever. I am a big believer of the fact that one needs to monitor and take a look at the logs from time to time. It is the best way to mitigate problems before they occur. Recently I noticed the below error in SQL Server ERRORLOG. I wanted to know the exact meaning of each and every placeholder in error message. Since I found it, I am sharing with you.

Error number: 18100

SELECT TEXT, severity, is_event_logged
FROM  sys.messages
WHERE message_id = 18100
AND   language_id = 1033


Process ID %d was killed by hostname %.*ls, host process ID %d.
As per my understanding %d stands for number and %ls stands for string.

Whenever a KILL command is issued in SQL Server, it is logged into SQL Server Errorlog.

1. First placeholder would be the SPID which was KILL’ed.
2. Second placeholder is the machine name from where the KILL command was issued. Note that host name can be obfuscated and not guaranteed to be exact name of the machine. While making connection, we can pass our own host name.
3. Third placeholder is the client Process ID which has issued the kill command. This can be seen via Task Manager.

If we look at Errorlog, we can also find the SPID which has issued the kill command.

2015-07-14 05:00:00.290 spid52       Process ID 51 was killed by hostname BIGPINAL, host process ID 10044.
Killed by : SPID52
Killed : 51
Host Name – BIGPINAL
PID in Task Manager – 10044

I have seen few DBA putting schedule job to monitor blocking and kill it. In that case you would see Process ID from SQL Agent. One of the interesting piece which I was not able to answer was the question asked by a blog reader on https://blog.sqlauthority.com/2007/06/26/sql-server-explanation-and-example-four-part-name/

I am getting messages in errorlog as Process ID 400 was killed by hostname , host process ID 0.
there is no hostname , and process ID 0, when i checked for process id in task manager , 0 is for system idle process.

This made me think and I spoke to few local SQL Server Experts and they asked me the exact message in ERRORLOG and I got below as reply

2015-07-01 01:22:45.600 spid21s      Process ID 51 was killed by hostname , host process ID 0.

This means that system SPID 21s has killed the process 51 and since this is a system SPID, we would not see host name and host process.

Can you think of a way to reproduce this behavior and get similar message in SQL ERRORLOG where hostname is empty and host process ID is zero? If you can write the same over comments, it would be of great help for others too.

Reference: Pinal Dave (https://blog.sqlauthority.com)