March 27, 2012 at 5:59 am
I am trying to delete an old database. When I first tried, I got an error that it was being used, so I used
ALTER DATABASE DB
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
Still couldn't delete, it gave me the same error:
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.
Now, when I use the ALTER code above to try to set it back to Multi_User it won't let me? Anything I try to do, I get the same error above.
Anyone have any ides on how I can get this DB deleted?
Thanks.
March 27, 2012 at 6:03 am
select * from sys.sysprocesses where dbid=""
kill spid
kill all spids with drop database option at last.
for ex ,
KILL 66
KILL 68
KILL 77
KILL 89
Drop database <DBNAME>
GO
select whole thing at a time and execute
🙂
March 27, 2012 at 6:03 am
Quick, dirty and ugly way (if you are desperate).
Stop SQL Service, drop the mdf and ldf files manually.
Start SQL, note the corrupt DB, right click delete.
OR:
sp_who2
Check who/what is connected to the db.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 27, 2012 at 6:09 am
Thanks! Killing the process worked.
March 27, 2012 at 6:20 am
Not happy that it worked, that was the worst advise I've ever given.
But, I have had to use it on non-production systems once or twice as well.
Never, ever, ever, use this method on critical systems.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 27, 2012 at 6:22 am
Yes, I understand. This is an old DB that hasn't been used in years.
March 27, 2012 at 9:16 am
Henrico
What should be the approach for critical systems then? I believe Setting user to single_mode with immediate rollback will also kill processes and rollback all open transactions.
Stopping service should be doing somewhat similar thing.
Shall we rollback asfter putting some wait?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
March 27, 2012 at 9:28 am
The first thing to do is find out what else is accessing this database. Ex. The OP said it was old an unused, but clearly something was accessing it.
Also, If you open a query window and run the single_user statement with rollback immediate... Don't open another window and try to connect to the database, it will not work because that window is a second session.
Jared
CE - Microsoft
March 27, 2012 at 10:24 am
But as Henroci mentioned, kill spid on live server is a bad idea. And putting databse in single user mode will be doing that in background. So what we should be doing ideally then?
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.
March 27, 2012 at 10:30 am
Find out what is using that process before doing anything and find out why it is still accessing the database.
Jared
CE - Microsoft
March 27, 2012 at 11:20 am
FYI...In my case it was an sa login that was connected? I put it in single_user mode and still couldn't delete the DB. Also, it wouldn't let me put it back into multi_user mode.
March 27, 2012 at 11:23 am
Just guessing... but did you put the database into single user using the query window, then try to delete the database by right-clicking on it and selecting delete? (2 connections; 1 in query window and another from the object explorer)
Jared
CE - Microsoft
March 27, 2012 at 11:26 am
SQLKnowItAll (3/27/2012)
Just guessing... but did you put the database into single user using the query window, then try to delete the database by right-clicking on it and selecting delete? (2 connections; 1 in query window and another from the object explorer)
i ran into that same problem. was trying to restore and had a query window open setting the DB to single user (ALTER...). i about smacked my self when i realized what was going on.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 27, 2012 at 11:31 am
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.
March 27, 2012 at 11:37 am
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.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply