Post reply

Kill a spid in EM

  • 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

  • October 23, 2006 at 2:22 pm

    #667449

    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

  • October 23, 2006 at 3:12 pm

    #667454

    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?

  • October 23, 2006 at 4:25 pm

    #667465

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

    -SQLBill

  • October 23, 2006 at 6:32 pm

    #667486

    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 ?

     

     

  • October 24, 2006 at 3:43 am

    #667515

    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.

  • October 24, 2006 at 6:21 am

    #667534

    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.

  • October 24, 2006 at 8:38 am

    #667598

    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

     

  • October 24, 2006 at 8:43 am

    #667599

    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.

  • October 24, 2006 at 9:23 am

    #667618

    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.

  • October 24, 2006 at 9:42 am

    #667623

    Thanks!!

  • October 24, 2006 at 9:46 am

    #667626

    Did you refresh your view?

    -SQLBill

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

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