Script to kill all processes using database


The below script will “kill all the proccesses that use database ” which name in set to @dbname variable

declare @dbname sysname

set @dbname = ‘dbname’ — type here the name of database you want to kill processes of

declare

     @spid INT

    ,@Query VARCHAR(250)

    ,@processes CURSOR

SET @processes = CURSOR FOR

select spid

from master..sysprocesses

where db_name(dbid) = @dbname

open @processes

fetch next from @processes into @spid

while @@FETCH_STATUS = 0

begin

    set @query = ‘kill ‘ + cast(@spid as varchar(10))

    exec(@query)

    fetch next from @processes into @spid

end

close @processes

deallocate @processes

GO

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: