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

SQL SERVER - Process ID X was killed by hostname ABC, host process ID Y kill-01

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

SQL SERVER - Process ID X was killed by hostname ABC, host process ID Y kill-02

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.

SQL SERVER - Process ID X was killed by hostname ABC, host process ID Y kill-03

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)

Previous Post
SQL SERVER – Querying Performance Counters from SQL Server
Next Post
SQL SERVER – Enhancing Reference Data in Master Data Services – Notes from the Field #089

Related Posts

No results found.

4 Comments. Leave new

  • yogigollapudi
    July 15, 2015 8:06 pm

    Below are today’s errors from error log, even i have not found any clues in the trace file for these times.
    Date 7/15/2015 8:34:22 AM
    Log SQL Server (Current – 7/10/2015 3:03:00 PM)
    Source spid31s
    Message
    Process ID 251 was killed by hostname , host process ID 0.
    ——————————————————
    Date 7/15/2015 6:01:43 AM
    Log SQL Server (Current – 7/10/2015 3:03:00 PM)
    Source spid30s
    Message
    Process ID 292 was killed by hostname , host process ID 0.

    Reply
  • Hi, is there a way to determin what the killed spid was trying to do, i.e. what SQL it was trying to run?

    Reply
  • I my case Sql Server pid xxx killed the sql server spid yy while running the weekend maintenance job. Rebuild index.

    Reply
  • I meant sql server agent pid as xxx. I am not sure about this strange behavior. Did anyone open a case with MS?

    Reply

Leave a Reply

Menu