SQL Server error logs are a valuable resource for database administrators and developers. They provide insights into the health and performance of the SQL Server instance, as well as any errors or issues that may have occurred. Monitoring and reviewing error logs regularly can help identify and resolve problems before they become critical.

In this article, we will explore a specific error message that appears in the SQL Server error log and decipher its meaning. The error message we will focus on is:

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.

Let’s break down the placeholders in this error message:

• %d: This placeholder represents a number. In the context of this error message, it refers to the SPID (Server Process ID) that was killed.
• %.*ls: This placeholder represents a string. It represents the hostname from where the KILL command was issued. It’s important to note that the hostname may be obfuscated and may not necessarily reflect the exact name of the machine.
• %d: This placeholder represents another number. It represents the client Process ID that issued the KILL command. This can be obtained from the Task Manager.

Whenever a KILL command is executed in SQL Server, it is logged into the SQL Server error log. The error log entry includes the SPID that was killed, the hostname from where the command was issued, and the client Process ID.

It’s worth mentioning that some DBAs schedule jobs to monitor and kill blocking processes. In such cases, the Process ID from the SQL Agent may be seen in the error log entry.

Now, let’s address a question raised by a blog reader regarding an error log entry with an empty hostname and a host process ID of 0:

Process ID 400 was killed by hostname , host process ID 0.

This particular error log entry indicates that the system SPID 21s killed process 51. Since this is a system SPID, the hostname and host process ID are not displayed. The host process ID of 0 corresponds to the system idle process.

If you have encountered a similar error log entry and would like to reproduce this behavior, please share the details in the comments section. Your input will be valuable for others facing the same issue.

Remember, regularly monitoring and reviewing SQL Server error logs can help you proactively identify and resolve issues, ensuring the smooth operation of your SQL Server instance.