Tuesday, March 27, 2012

Clear connections to DB

Hello,
In enterprise manager when detaching DB, there is an option to clear open
connections without actually detaching a database.
I'm looking for the sp with the ability of clearing cconnections.
I failed looking it up in books on line.
I'm using SQL 2000
I would be grateful for any help
Thanks,
Gary
Gary
KILL command
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:6E40C618-73B0-4BB0-9B8E-FB4A83BD5199@.microsoft.com...
> Hello,
> In enterprise manager when detaching DB, there is an option to clear open
> connections without actually detaching a database.
> I'm looking for the sp with the ability of clearing cconnections.
> I failed looking it up in books on line.
> I'm using SQL 2000
> I would be grateful for any help
> Thanks,
> Gary
|||If the suggested KILL is too time-consuming as you have to kill all the
connections you can use:
ALTER DATABASE <Name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
HTH, jens Suessmeyer.
|||Gary,
if you want to drop all the connections and prevent new ones:
"alter database YOURDB SET single_user with rollback immediate"
If you just want to drop all the existing connections without preventing new
ones, then you can cursor through master.dbo.sysprocesses, filter on
database and use exec to run the KILL command. If you want to go down this
route, have a go at the cursor and post it up.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hello Uri,
Kill terminates process, I need to terminate all connections to the specific
databse.
Thanks
Gary
"Uri Dimant" wrote:

> Gary
> KILL command
>
> "Gary" <Gary@.discussions.microsoft.com> wrote in message
> news:6E40C618-73B0-4BB0-9B8E-FB4A83BD5199@.microsoft.com...
>
>
|||Gary
KILL can be used to terminate a normal connection, which internally
terminates the transactions associated with the given SPID
"Gary" <Gary@.discussions.microsoft.com> wrote in message
news:B9D5EA96-C63D-452B-8082-62CFDF5CBC96@.microsoft.com...[vbcol=seagreen]
> Hello Uri,
> Kill terminates process, I need to terminate all connections to the
> specific
> databse.
> Thanks
> Gary
> "Uri Dimant" wrote:

No comments:

Post a Comment