T-SQL CLOSE Connection to DB

[coosa]

How do i close a current connection to a database using t-sql?
I fail some time to drop the database getting messages that it's
currently in use.
Using the wizard to delete the database, i could check the option to
close all connections to the db, but how do i do it using t-sql?

best regards

[Erland Sommarskog]

Re: T-SQL CLOSE Connection to DB

coosa (coosa76@gmail. com) writes:[color=blue]
> How do i close a current connection to a database using t-sql?[/color]

USE <someotherdb>



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[coosa]

Re: T-SQL CLOSE Connection to DB

I did actually,

USE [Master];
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'MyDB')
BEGIN
PRINT N'Database [MyDB] already exists';
DROP DATABASE MyDB;
END
GO

[coosa]

Re: T-SQL CLOSE Connection to DB

Similary, is there a similar command like 'disconnect' such as in DB2?

[Erland Sommarskog]

Re: T-SQL CLOSE Connection to DB

coosa (coosa76@gmail. com) writes:[color=blue]
> Similary, is there a similar command like 'disconnect' such as in DB2?[/color]

I don't know what DISCONNECT in DB2, but the only way to disconnect from
the server with a T-SQL command is SHUTDOWN WITH NOWAIT. Or a RAISERROR
with a severity level >= 20. I would not recommend any of them.

The proper way to disconnect is to do it from the client.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Hugo Kornelis]

Re: T-SQL CLOSE Connection to DB

On 27 May 2006 19:48:27 -0700, coosa wrote:
[color=blue]
>How do i close a current connection to a database using t-sql?
>I fail some time to drop the database getting messages that it's
>currently in use.[/color]

Hi coosa,

If you want to disconnect YOUR OWN connection to a database, check out
Erland's reply.

If you need to do maintenance but can't because OTHER people still have
open connections to a database, then you might want to use one of the
following

ALTER DATABASE <dbname> SET SINGLE_USER

or

ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK_IMMEDI ATE

The first version will allow curent connection to finish their business
and commit their work. The latter option immediately disconnects all
open conenctions and rolls back any changes from unfinished
transactions.

--
Hugo Kornelis, SQL Server MVP

[coosa]

Re: T-SQL CLOSE Connection to DB

That might be it; some thing remotly is still in use. Then, is there a
way to determine which connections are being used and wait for them but
prevent any new connections?

[Hugo Kornelis]

Re: T-SQL CLOSE Connection to DB

On 28 May 2006 18:46:15 -0700, coosa wrote:
[color=blue]
>That might be it; some thing remotly is still in use. Then, is there a
>way to determine which connections are being used[/color]

Hi coosa,

EXEC sp_who2;
[color=blue]
> and wait for them but
>prevent any new connections?[/color]

ALTER DATABASE <dbname> SET SINGLE_USER;

(By _not_ adding the WITH ROLLBACK_IMMEDI ATE option, you tell SQL Server
to disallow new connections but wait until existing connections are
broken before setting the DB to single user)

Note that many front-end programs keep their connection open, mostly
being idle while the person on the screen enters data, answers a phone
call or visits the water cooler. If you waiting for those connections to
close, you won't have your DB in single-user state before the office
closes.

--
Hugo Kornelis, SQL Server MVP

[coosa]

Re: T-SQL CLOSE Connection to DB

It's interesting what's happening ...
i run:
USE Master;
GO
EXEC sp_who2;
GO

The 'MyDb' is still under the status RUNNABLE for the command 'SELECT
INTO' under the ProgamName 'Microsoft SQL Server Management Studio -
Query'.
I run the command again after a minute and it disappears.
It seems when i swith the use to a different DB, the change has no
IMMEDIATE effect.
Again, using the "Management Studio", by right clicking the Database
Name and choosing to "Delete", two check boxes can be selected and the
latter is "Close existing Connections" and it never failed to delete.
I have used the suggestion of usning both "ALTER DATABASE <dbname> SET
SINGLE_USER WITH ROLLBACK_IMMEDI ATE" and "ALTER DATABASE <dbname> SET
SINGLE_USER" but it's the same. Erland suggestion recommnds stoping the
entire server which i can't afford since there are other databases
running.

[Hugo Kornelis]

Re: T-SQL CLOSE Connection to DB

On 29 May 2006 08:22:10 -0700, coosa wrote:
[color=blue]
>It's interesting what's happening ...
>i run:
>USE Master;
>GO
>EXEC sp_who2;
>GO
>
>The 'MyDb' is still under the status RUNNABLE for the command 'SELECT
>INTO' under the ProgamName 'Microsoft SQL Server Management Studio -
>Query'.
>I run the command again after a minute and it disappears.
>It seems when i swith the use to a different DB, the change has no
>IMMEDIATE effect.[/color]

Hi coosa,

Very strange. I have never experienced or heard this before. And I was
unable to reproduce - when I ran the code above, sp_who2 reported the
connection to be runnable in the master DB.
[color=blue]
>Again, using the "Management Studio", by right clicking the Database
>Name and choosing to "Delete", two check boxes can be selected and the
>latter is "Close existing Connections" and it never failed to delete.[/color]

Under the hood, Management Studio uses the ALTER DATABASE command I
suggested, with the ROLLBACK_IMMEDI ATE option. This is easy to verify:
make a DB, open some windows in MS to connect to this test DB, then
right-click the DB, click "Delete", check "Close existing connections",
then instead of clicking "OK", click "Script / Script to Clipboard".
Finally, paste the contents of the clipboard in a query window or in a
text file. Here's what was generated on my computer:

EXEC msdb.dbo.sp_del ete_database_ba ckuphistory @database_name = N'Temp'
GO
USE [master]
GO
ALTER DATABASE [Temp] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
/****** Object: Database [Temp] Script Date: 05/30/2006 00:53:16
******/
DROP DATABASE [Temp]
GO
[color=blue]
>I have used the suggestion of usning both "ALTER DATABASE <dbname> SET
>SINGLE_USER WITH ROLLBACK_IMMEDI ATE" and "ALTER DATABASE <dbname> SET
>SINGLE_USER" but it's the same.[/color]

What does "the same" mean? Do you get any error messages? If so, what
messages?

What happpens if you open a query window in SSMS, then type (or copy)
and execute the query below (replacing MyDB [twice!] with the actual
name of the DB you want to drop). If you get any errors, please copy and
paste the exact messages into a reply to this message (unless you're
running a localized Cyrillic or similar installation - in that case, a
translation is actually preferred <g>)

USE master
go
ALTER DATABASE MyDB SET SINGLE_USER WITH ROLLBACK_IMMEDI ATE
go
DROP DATABASE MyDB
go

Of course, you should replace MyDB with the real name of your database
(two times!)
[color=blue]
>Erland suggestion recommnds stoping the
>entire server which i can't afford since there are other databases
>running.[/color]

Erland though you were asking how to force connection to the _SERVER_ to
be broken. For dropping a database, it suffices to break the connection
to the _database_. I know Erland well enough to be 100% sure that he'd
never recommend shutting down a server to drop connections to a DB.

--
Hugo Kornelis, SQL Server MVP

[coosa]

Re: T-SQL CLOSE Connection to DB

my appologies then to Erland for this misunderstandin g.