Printable View
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
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
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