Re: T-SQL CLOSE Connection to DB
- From: Hugo Kornelis <hugo@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 30 May 2006 01:02:34 +0200
On 29 May 2006 08:22:10 -0700, coosa wrote:
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.
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.
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.
Under the hood, Management Studio uses the ALTER DATABASE command I
suggested, with the ROLLBACK_IMMEDIATE 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_delete_database_backuphistory @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
I have used the suggestion of usning both "ALTER DATABASE <dbname> SET
SINGLE_USER WITH ROLLBACK_IMMEDIATE" and "ALTER DATABASE <dbname> SET
SINGLE_USER" but it's the same.
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_IMMEDIATE
go
DROP DATABASE MyDB
go
Of course, you should replace MyDB with the real name of your database
(two times!)
Erland suggestion recommnds stoping the
entire server which i can't afford since there are other databases
running.
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
.
- Follow-Ups:
- Re: T-SQL CLOSE Connection to DB
- From: coosa
- Re: T-SQL CLOSE Connection to DB
- References:
- T-SQL CLOSE Connection to DB
- From: coosa
- Re: T-SQL CLOSE Connection to DB
- From: Hugo Kornelis
- Re: T-SQL CLOSE Connection to DB
- From: coosa
- Re: T-SQL CLOSE Connection to DB
- From: Hugo Kornelis
- Re: T-SQL CLOSE Connection to DB
- From: coosa
- T-SQL CLOSE Connection to DB
- Prev by Date: Re: sp_helprotect COLUMN has NULL value
- Next by Date: Help Needed
- Previous by thread: Re: T-SQL CLOSE Connection to DB
- Next by thread: Re: T-SQL CLOSE Connection to DB
- Index(es):
Relevant Pages
|
