Thursday, May 25, 2006

Killing all processes in the database 'DBNAME' except those connected throw OSQL in Sql Server

I have to restore a snapshot of a Sql server database daily used in a web site,to do this i must be in exclusive mode and the only user connected to the database, so i came up with this script to kill all processes connected to 'DBNAME' , except connected throw OSQL (my connection):
First of all let's create a procedure to kill db processes:

use master
go
CREATE proc kill_process(

@dname varchar(20))
as
Declare

@did int,
@sid int,
@s nvarchar(128)
select @did = dbid
from master..sysdatabases
where name = @dname
declare spid_cursor cursor for
select spid
from master..sysprocesses
where dbid = @did
and program_name <> 'OSQL-32'
open spid_cursorfetch next
from spid_cursor
into @s
While @@fetch_status = 0
Begin
Select @s = 'Kill '+convert(nvarchar(30),@sid)
exec(@s)
--print @s
fetch next
from spidcurs
into @sid
End
Deallocate spidcurs
GO

Now how to use this procedure:

USE MASTER
go
--Killing all processes in the database 'DBNAME' except those connected throw OSQL

exec proc kill_process 'DBNAME'
go
---set the database to single user mode

sp_dboption 'DBNAME', 'single user', true

now let's do the database restore,and don't forget to remove single user mode while finishing

sp_dboption 'DBNAME', 'single user', false

No comments: