Kill a spid in EM

Question

Kill a spid in EM

Diana Tan

SSC Journeyman

Points: 90
More actions
October 23, 2006 at 2:12 pm

#174975

I have a spid process which blockd the other 3 processes. When I try to kill this SPID from EM, it works fine, but when refreshing the screen, this process apprears again.
Why this process cannot be killed (without any error when kill it in EM) ?
Is there any way to force killing?
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sandeep Kataria Valued Member Points: 56 More actions · Answer 1

hi,

this is not mine so please dont give me credit for it, but it worked for me so hopefully it will for you too. this script is from this site...

also look for USP_KillUsers on this site.....

Sandeep

--enhanced 02/04/2005 to also list hostname

CREATE PROCEDURE sp_Kill

@DBNAME VARCHAR(30)

--Stored procedure to Delete SQL Process

AS

BEGIN

SET NOCOUNT ON

DECLARE @SPID INT

DECLARE @STR NVARCHAR(50)

DECLARE @HOSTNAME NVARCHAR(50)

CREATE TABLE #TMPLOG (

SPID INT,

ECID INT,

STATUS VARCHAR(50),

LOGINAME VARCHAR(255),

HOSTNAME VARCHAR(50),

BLK INT,

DBNAME VARCHAR(30),

CMD VARCHAR(100)

INSERT INTO #TMPLOG EXEC SP_WHO

IF @@ERROR <> 0 GOTO Error_Handle

DECLARE CURPROCESSID CURSOR FOR SELECT SPID FROM #TMPLOG

WHERE DBNAME LIKE @DBNAME

OPEN CURPROCESSID

FETCH NEXT FROM CURPROCESSID INTO @SPID

SELECT @HOSTNAME=HOSTNAME FROM #TMPLOG WHERE SPID=@SPID

IF @SPID IS NOT NULL

PRINT 'Spid Process Kill List For database: ' + @dbName

ELSE

PRINT 'NO Processes Exist to be killed on database ' + @dbName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @STR = 'KILL ' + CONVERT(VARCHAR,@SPID)

EXEC SP_EXECUTESQL @STR

PRINT convert(varchar,@spid) + ' - ' + @HOSTNAME

IF @@ERROR <> 0 GOTO ERROR_HANDLE

FETCH NEXT FROM CURPROCESSID INTO @SPID

END

Error_Handle:

IF @@ERROR <> 0 PRINT 'Error killing process - ' + convert(varchar,@spid) + ' - ' + @HOSTNAME

drop table #tmpLog

SET NOCOUNT OFF

END

Kind Regards,

Sandeep Kataria

Diana Tan SSC Journeyman Points: 90 More actions · Answer 2

Thanks for the help. If the killed process is still showed in EM, I guess one possible way may be the process is doing 'rollback'.

Is there any way from SQL server EM we can identify the killed process is doing rollback or the process does not get killed?

From EM process_info the status only show running/sleeping. IS there any status show rollback or killed something?

SQLBill SSC Guru Points: 51440 More actions · Answer 3

Right click on the process and select Properties. That will show you all or most of what command is running.

-SQLBill

Diana Tan SSC Journeyman Points: 90 More actions · Answer 4

yes, I saw the command this process was running, but my quesiton is after this process is KILLed from EM without any error, why this process is still showing on the process info window with the same command showing from Properties?

Is there a place I can check if this process is running rollback or where I can see the status of this KILLed process marked as 'KILLED' or smonthing else ?

Clive Strong SSChampion Points: 11222 More actions · Answer 5

Could be 2 things.

1) the spid is rolling back...From query analyser, run kill <spid> with rollback. This will give you a percentage of rollback completion.

2) the spid could have been picked up and used by another process.

PurpleLady SSCertifiable Points: 7819 More actions · Answer 6

I have a similar problem on occasion with rogue processes. However, once I kill them, the process shows Killed/Rollback in the status. There are times when I have to stop and restart the agent in order to get the agent to refresh. Usually, when the agent restarts, the process is gone.

Diana Tan SSC Journeyman Points: 90 More actions · Answer 7

Thanks all for the inputs. I really appreciate your helps.

So SQL server does have killed/rollback type of status. I do not understand why my killed process was still showed as 'sleeping' status, but changed to killed or rollback? Maybe I need to refresh the agent?

I will try to use command to check the rollback %.

If a process is in sleeping status, how to tell this process is active or inactive? Is sleeping=inactive means doing nothing?

Thanks

Clive Strong SSChampion Points: 11222 More actions · Answer 8

sleeping is either waiting for user input - for example, a query analyser session which is left open.

it can also be a spid waiting for a lock.

vadba SSChampion Points: 11133 More actions · Answer 9

Actually, the problem is the both QA and EM will automatically reconnect a killed connection. For example, if you kill a QA connection using EM, then refresh the Current Actvity, you'll see that conenction has disappeared. But as soon a a query is run, it will reappear. It will even take on a new SPID if the old one is in use by another connection.

Diana Tan SSC Journeyman Points: 90 More actions · Answer 10

Diana Tan

SSC Journeyman

Points: 90

October 24, 2006 at 9:42 am

#667623

Thanks!!

SQLBill SSC Guru Points: 51440 More actions · Answer 11

SQLBill

SSC Guru

Points: 51440

October 24, 2006 at 9:46 am

#667626

Did you refresh your view?

-SQLBill