Kill Process (sp_who2)

When killing any running SQL Server process, it is the durable part of the ACID test we care most about. The durability criterion requires that when a process is killed without completing, all data changed by any uncompleted transactions must be returned to the state it was in before the transaction started. The process of returning data to its original state is called rollback.

What this means is that when you kill a running process in SQL Server, all the uncommented changes made by this process are going to be undone. In most cases, the rollback process happens very quickly and does not have a major impact. In cases where there are a large number of uncompleted transactions, the rollback process can take some time and have a major impact on the performance of the database during that time.

Identify SPID
To check which processes are affecting the server performance, run the following command:

This shows all the sessions that are currently in the database. These are shown as SPID’s or server process Id’s. The first 50 records that are returned are are system SPIDs and user processes are SPID numbers after 50. When looking into the performace of the server, these SPIDs are most likely causing the issue.

Kill Process
To kill a process just enter the word kill followed by the SPID:

The field Blkby will identify the SPID that is causing any blocks.

Lock Info
To view more details on the lock process just enter the follwing: