Kill–use this weapon carefully in SQL SERVER . TIP #90

When we heard “Kill” then first impression of this word is very bad. We always scare with this word.

In real world we never want this action  from anyone but in SQL Server case it is very helpful and help us many times.

This feature we can use but very carefully. It is something like we have to kill a cruel giant not the common innocent person.

Lets understand this by an example.

Suppose we wrote a stored procedure or function and by mistake we wrote a condition which is never ending. We didn’t realize this and when we run the stored procedure/function the SQL Server hangs , system is slow and many other issues occur and our stored procedure which we run still running and laughing on us.

In such situation we have to kill that particular stored procedure execution statement which is cruel giant and eating the resources.

See below statement

WHILE 1=1
BEGIN
  SELECT 'Infinite Loop'
END

It is never ending loop. and it is running on our SQL SERVER and will never stop.

Now first step is to Identify the process id for this statement for this we can use either sp_Who or sp_who2 which we discussed in TIP #82.

Now when we run it you will find on Database “IndiandotnetDB” we have a SPID 53 which is last statement on below image. This SPID 53 is reference of above while loop which we have to stop any ways.

Now we know SPID so we can stop or KILL  as shown below the syntax is very simple of KILL

KILL SPID

Now we are good to go.

Note: Please use this KILL statement very carefully.

I hope this tip will help you some where to KILL or stop SQL SERVER enemies.

Enjoy !!!

RJ!!!