How to check who has killed the job which was running?if logs are not generated
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. what is sql server kill process?
For sql server kill process, you can use the KILL SPID command to kill a particular user session. You can only KILL the user processes. Once we kill a session, it undergoes through the rollback process, and it might take time and resources as well to perform a rollback. I suggest you 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
Answer (1)
If logs are not generated and you need to determine who killed a job that was running, it can be challenging to identify the culprit directly. However, there are a few strategies you can try:
Check Job History: Some systems maintain a history of job executions, including information about who triggered or terminated the job. Check if your system or job scheduler keeps such logs or history records. If available, they might provide insights into who interacted with the job.
Audit Trail: Review your system's audit trail or activity logs. Look for any relevant entries around the time the job was terminated. While these logs may not explicitly state who terminated the job, they might provide clues or context about the activity on the system.
Access Control: If your system has access controls or permissions management, check if there are any users or roles with the authority to terminate jobs. Review access logs or permission changes to see if any user or role with such permissions interacted with the job.
Communication Channels: Reach out to your team or colleagues who have access to the system. Sometimes, someone might have terminated the job due to an emergency, system maintenance, or other valid reasons. By communicating with others, you might be able to identify the person responsible.
Alerting Mechanisms: If your system has alerting mechanisms or notifications for job terminations or abnormal activities, review any alerts or notifications that were triggered around the time the job was terminated. These alerts might contain information about who triggered the termination.
Review System Changes: Check if there were any recent changes or updates to the system, job scheduler, or related components. Sometimes, changes in configurations, updates, or patches can inadvertently affect job executions or termination behavior.
Security Incident Investigation: If you suspect malicious activity or unauthorized access, consider conducting a security incident investigation. This might involve analyzing system logs, access controls, and other security-related data to determine if the job termination was part of a security incident.
If none of these strategies yield conclusive results, it might be challenging to determine who terminated the job without logs or explicit records. In such cases, you may need to implement additional monitoring, logging, or auditing mechanisms to track future job executions and terminations effectively.