Kill

I need to let a third party security app run a script as a
system admin to drop and recreate a database. Before this
will run, of course I need to make sure all connections to
that database are dropped.
Is there a command that will kill all connections to a
database?Sometimes you just have to trace other programs that can do this. I traced
what happens when you disconnect a database and someone is using it.
select spid from master..sysprocesses where dbid=db_id('<database name>')
Then that spid result is fed to a kill statement.
Should warn you that this is a tricky thing that you are doing. Certain
kinds of connections, such as those with SQL Query Analyzer and Enterprise
Manager, do not drop very easily. Sometimes connections keep going. A
drastic step might be to use a net stop/net start to restart MSSQLserver.
That will certainly free up all the connections, though the database might
go into recovery.
But no, if the Clear connection button on the Detach Database function in
SQL EM doesn't call a command, I doubt you are going to find one.
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"gotit" <anonymous@.discussions.microsoft.com> wrote in message
news:00a701c3d3b6$6be8e6c0$a401280a@.phx.gbl...
> I need to let a third party security app run a script as a
> system admin to drop and recreate a database. Before this
> will run, of course I need to make sure all connections to
> that database are dropped.
> Is there a command that will kill all connections to a
> database?|||Add these lines to the top of the script.
ALTER DATABASE 'MyDBName' SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE 'MyDBName' SET ONLINE
GO
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"gotit" <anonymous@.discussions.microsoft.com> wrote in message
news:00a701c3d3b6$6be8e6c0$a401280a@.phx.gbl...
> I need to let a third party security app run a script as a
> system admin to drop and recreate a database. Before this
> will run, of course I need to make sure all connections to
> that database are dropped.
> Is there a command that will kill all connections to a
> database?|||I know that I've seen a stored procedure on the net that will kill all
user connections. Try doing a search in google for something like "sp
kill all users" without the quotes.
Aaron
Andy Svendsen wrote:
> Sometimes you just have to trace other programs that can do this. I traced
> what happens when you disconnect a database and someone is using it.
> select spid from master..sysprocesses where dbid=db_id('<database name>')
> Then that spid result is fed to a kill statement.
> Should warn you that this is a tricky thing that you are doing. Certain
> kinds of connections, such as those with SQL Query Analyzer and Enterprise
> Manager, do not drop very easily. Sometimes connections keep going. A
> drastic step might be to use a net stop/net start to restart MSSQLserver.
> That will certainly free up all the connections, though the database might
> go into recovery.
> But no, if the Clear connection button on the Detach Database function in
> SQL EM doesn't call a command, I doubt you are going to find one.
>sql