|
|
Posted by Hugo Kornelis on 05/30/06 02:02
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
Navigation:
[Reply to this message]
|