Results 1 to 3 of 3

Thread: How to kill process

  1. #1
    Natalia Guest

    How to kill process

    SQL Server 2000.

    Hi!
    After I killed maintenance process (57) in the current activity window and run ‘kill 57 with statusonly’ I got message: ‘SPID 57: Estimated rollback completion: 100%. Estimated time remaining: 0 sec.’
    When I reopened current activity window I still see that SPID 57 is runable.
    Then I run select*from
    sysprocesses where SPID = 57
    It also reports that process status is runable.
    What is the problem?
    How can I remove (and should I) this record from masterdb.
    Thank you,
    Natalia


  2. #2
    jason Guest

    How to kill process (reply)

    I've found that if the process you are trying to kill is in the master database, you need to stop and start sql server in order to get rid of it. This was definitely the case in sql 7, and i've not been able to find any other way to do this in sql 2K.
    j


    ------------
    Natalia at 4/5/01 2:07:15 PM

    SQL Server 2000.

    Hi!
    After I killed maintenance process (57) in the current activity window and run ‘kill 57 with statusonly’ I got message: ‘SPID 57: Estimated rollback completion: 100%. Estimated time remaining: 0 sec.’
    When I reopened current activity window I still see that SPID 57 is runable.
    Then I run select*from
    sysprocesses where SPID = 57
    It also reports that process status is runable.
    What is the problem?
    How can I remove (and should I) this record from masterdb.
    Thank you,
    Natalia


  3. #3
    Join Date
    Oct 2007
    Posts
    1
    That is what you can do. www.closeoutmerchant.com


    CREATE TABLE #TmpWho
    (
    spid INT,
    -- ecid INT,
    status VARCHAR(150),
    loginame VARCHAR(150),
    hostname VARCHAR(150),
    blk INT,
    dbname VARCHAR(150),
    cmd VARCHAR(150)
    )
    DECLARE @CNT int

    /*DELETE ENTRIES FROM TEMP TABLE*/
    DELETE #TmpWho

    /*RUN PROC AND INSERT TO TEMP TABLE*/
    INSERT INTO #TmpWho
    EXEC sp_who
    -- select * from #TmpWho

    /*CHECK COUNT*/
    SELECT @Cnt = COUNT(*) from #TmpWho WHERE (dbname IN ('DB_1', 'DB_2'))

    While @Cnt<>0
    BEGIN
    DECLARE @spid INT
    DECLARE @tString varchar(15)
    DECLARE @getspid CURSOR

    SET @getspid = CURSOR FOR
    SELECT spid
    FROM #TmpWho
    WHERE (dbname IN ('DB_1', 'DB_2'))
    OPEN @getspid
    FETCH NEXT FROM @getspid INTO @spid
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @tString = 'KILL ' + CAST(@spid AS VARCHAR(5))
    -- PRINT(@tString)
    EXEC(@tString)
    FETCH NEXT FROM @getspid INTO @spid
    END
    CLOSE @getspid
    DEALLOCATE @getspid

    /*CHECK IF SP_WHO GIVE YOU ANYTHING BACK*/
    DELETE #TmpWho

    /*RUN PROC AND INSERT TO TEMP TABLE*/
    INSERT INTO #TmpWho
    EXEC sp_who

    /*CHECK COUNT*/
    SELECT @Cnt = count(*) from #TmpWho WHERE (dbname IN ('DB_1', 'DB_2'))
    END

    SELECT * FROM #TmpWho WHERE (dbname IN ('DB_1', 'DB_2'))
    /*DROP TEMP TABLE*/
    DROP TABLE #TmpWho

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •