connections - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

connections

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-11-02 : 12:55:23

how do you forcibly drop all connections to a database?

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-02 : 13:12:15

You can use the kill command or kill them from enterprise manager under management, current activity

create proc s_KillConnections
@dbname varchar(128) ,
@program_name varchar(200) = null ,
@loginame varchar(200) = null
as
/*
s_KillConnections 'tradarbe'
*/
declare @dbid int
select @dbid = db_id(@dbname)

-- try to kill all the connections
declare @retries int ,
@sql varchar(8000)

select @retries = 5
while @retries > 0
begin
select @retries = @retries - 1

select @sql = ''
select @sql = @sql + 'kill ' + convert(varchar(10),spid) + ' '
from master..sysprocesses
where dbid = @dbid
and (program_name = @program_name or @program_name is null)
and (loginame = @loginame or @loginame is null)
and spid > 8 -- try to exclude system spids

if @sql <> ''
begin
exec (@sql)
waitfor delay '00:00:05'
end
end

GO

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-11-02 : 16:48:37

thanks nr, i've been using EM. But you can only do one at a time from rightmouse click. quite tedious when there's a hundred. Thanks for that sp. I'll add it to my arsenal.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources