February 4, 2009 at 7:40 am
I am writing a code, but before I run this code, I need to check if anyone is using that db, if yes kill that user? Is anyone know how to write this code? Thank you
February 4, 2009 at 8:12 am
you could use the following sql to get the session ids and then create a cursor to kill each spid
select spid from sysprocesses
where db_name(dbid) = @database_name
February 4, 2009 at 8:52 am
yulichka (2/4/2009)
I am writing a code, but before I run this code, I need to check if anyone is using that db, if yes kill that user? Is anyone know how to write this code? Thank you
Hi,
I have written a stored procedure for you..
CREATE PROCEDURE db_kill_allsp @dbname sysname
AS
BEGIN
DECLARE @spid smallint , @execstr nvarchar(10)
--Declare a cursor to get the list of SPID's using your DB.
DECLARE spids CURSOR FOR select spid from master..sysprocesses where db_name(dbid) = @dbname
OPEN spids
FETCH NEXT FROM spids into @spid
WHILE @@FETCH_STATUS = 0
BEGIN
SET @execstr = 'KILL '+ convert(char(4),@spid)
EXEC sp_executesql @execstr
FETCH NEXT FROM spids into @spid
END --End of While
CLOSE spids
DEALLOCATE spids
END --End of procedure
I hope the above sp will help you.
Rajesh Kasturi
February 4, 2009 at 8:55 am
Why open a cursor when you can do it in a simple way..try this..
alter database [db_name] set SINGLE_USER with rollback immediate
go
alter database [db_name] set MULTI_USER with rollback immediate
go
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
February 4, 2009 at 8:56 am
Thank you very much, But will it kill my process?
February 4, 2009 at 9:12 am
Yes that will kill the processes
The_SQL_DBA is right..
Rajesh Kasturi
February 4, 2009 at 9:16 am
yulichka (2/4/2009)
Thank you very much, But will it kill my process?
Once you are done with running the script I gave all other processes will be gone. And then you can run your code and if your connection needs to be killed too then you might know you can do it from Activity monitor..right click kill process on your SPID too...
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
February 4, 2009 at 9:23 am
Thank you
February 4, 2009 at 10:00 am
Do I change @dbname to my database name. I know I have change id?
February 4, 2009 at 10:52 am
yulichka (2/4/2009)
Do I change @dbname to my database name. I know I have change id?
In the code it has to be @dbname, then create the SP. When executing it mention the parameter as
Exec db_kill_allsp 'enter your database name here'
Thanks!!
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
February 4, 2009 at 10:57 am
Thak you
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply