Script to kill only connections to specific DB

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)

Script to kill only connections to specific DB

Author

Topic

CanadaDBA

583 Posts

Posted - 2005-04-27 : 16:40:30

Imagin there are two DBs and two applications in production and we need to remove all connections to only one of the DBs. I can stop and start but it recycles both DBs. How can I script to kill only those connections that are connected to specific DB?
If I do it in a loop based on information in sysobjects and Kill those specific connections, is it good approach?

Canada DBA

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-04-27 : 16:58:37

--To generate kill statements for all processes in 1 Database
--you may want to include some of the other commented out column names in your where clause

select 'kill ' + convert(varchar,spid)
--select hostname, program_name, nt_username, loginame
from master..sysProcesses
where dbid = db_id('MyDatabaseName')

Be One with the Optimizer
TG

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-04-27 : 18:23:33

Here's a script that can do the same, plus it can also kill connections by login name:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40077

CanadaDBA

583 Posts

Posted - 2005-05-02 : 11:32:36

What's the benefit of '; ' +char(13) + char(10) at the end of the command? I tried the command and it works with or without these characters.


  SELECT @SQLStr = 'kill ' + convert( VarChar(4), @spid)+ '; ' +char(13) + char(10)
  EXEC (@SQLStr)

quote:
Originally posted by robvolk

Here's a script that can do the same, plus it can also kill connections by login name:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40077

Canada DBA

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-02 : 16:20:21

Why go nuts

ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Brett

8-)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-05-02 : 18:38:32

The char(13)'s and such just make the output readable. You can copy and paste it into another window to run it too.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-05-03 : 00:41:14

quote:
Originally posted by X002548

Why go nuts

ALTER DATABASE <database name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Brett

8-)

or
alter database <dbname> offline
then online again

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources