Killing all the process
-
Hai,
When i am trying to restore my database, it is saying that some users are using the database so you cannot resotre.
when i went to enterprise manager --> current activity --> process info , i could see lots of process and out of that i need to find out which all are the process related to this database and kill them manually one by one..
finally i stopped and restarted my sql server instance for killing all the process related to this database..
Is there any query through which i can find out all user process related to a particular user database in and instance and kill them, without stopping the sql server instance and without disturbing the other databases running on the same instance ?
pls help
-
Hi,
I have a solution for you, i have created stored procedure which will kill's the process. For flexiblity i have added parameter 'DBName', so you can kill process for selected database.
CREATE PROCEDURE sp_KillProcess
@dbName varchar(30)
--AUTHOR : Deepak Sant
--Stored procedure to Delete SQL Process
AS
BEGIN
SET NOCOUNT ON
Declare @spid int
Declare @STR 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
IF @spid IS NOT NULL
PRINT 'Spid Process Killed List : '
ELSE
PRINT 'NO Process to Killed'
WHILE @@FETCH_STATUS = 0
BEGIN
SET @STR = 'KILL ' + convert(varchar,@spid)
EXEC sp_executesql @STR
PRINT @spid
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)
drop table #tmpLog
SET NOCOUNT OFF
END
GO
Hope this will help you
Regards,
Deepak Sant
Pune (India)
-
Here is a simple method to kill all processes related to a database,
declare @runcmd varchar(100), @dbconn int
declare dbconn cursor
for select spid from sysprocesses
where dbid=11 --Your database ID
open dbconn
fetch next from dbconn
into @dbconn
while @@fetch_status = 0
begin
set @runcmd='Kill ' + cast(@dbconn as varchar(3))
exec sp_sqlexec @runcmd
fetch next from dbconn
into @dbconn
end
CLOSE dbconn
DEALLOCATE dbconn
-
In similar situation, wherein while restoring the Database, I got error as ' some active users exist'. Hence I tried killing the user
using 'KILL spid'. Though the command executed successfully without any errors but
particular user not being killed. The user is not system user. It is a application user id.
When tried with 'DBCC INPUTBUFFER(spid)'
I came to know that the particular user is
executing some user defined stored procedure.
What would be reason for the process not being killed?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply