Having Trouble Deleting a DB

Question

Having Trouble Deleting a DB

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

capn.hector (3/27/2012)
djustice 20821 (3/27/2012)
This is the code that I used:
ALTER DATABASE DB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE db
GO
and the error that I got:
Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'readydesk' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
try the following:
ALTER DATABASE DB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
USE MASTER -- Change the database
GO
DROP DATABASE db
GO
im thinking the problem is you are still connnected to the database. by using "USE master" you are changing the database you are connected to.

Spot on capn.hector . You, djustice 20821, were still connected to the database in your query window.

Jared SSC Guru Points: 61793 More actions · Answer 2

djustice 20821 (3/27/2012)
This is the code that I used:
ALTER DATABASE DB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
GO
DROP DATABASE db
GO
and the error that I got:
Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'readydesk' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

You can't drop a database that you are connected to. That could be the issue. You would have to change the connection to MASTER.

Jared
CE - Microsoft

djustice 20821 Old Hand Points: 326 More actions · Answer 3

I actually tried that. After the above didn't work, I shut down SQL, logged back in and used the code:

USE MASTER

GO

DROP DATABASE db

GO

Received the same error about the DB is being used? The KILL process was a last resort.

Jared SSC Guru Points: 61793 More actions · Answer 4

Again, the point being that as soon as you logged off of SQL Server, something created a connection. You should have found out what that was...

Jared
CE - Microsoft

djustice 20821 Old Hand Points: 326 More actions · Answer 5

Gotcha, too late now, but I'll remember it for next time. Thanks again for your help.