February 3, 2010 at 4:35 pm
Thanks to all that take the slightest time to read or suggest any ideas.... super appreciated
I have a database that is stuck in recovery/read-only mode. I have tried everything i know from drop databasename to looking for locked SPIDs that are holding the database open and none exist. On most attempts to remove this database i get database in use and on a few i get database not found. If you run select * from master..sysdatabases you get all the database and this one has a status of 1073742976 while all the other have the 65544 status. I am now thinking i should just stop sql completely and then go and delete the mdf and ldf files then start the server back up but i have been told this is really bad(i know it is..and i dont know why so if you can explain this...it would be wonderful as well). Sooo any suggestions as to how i can stop whatever is causing this database to stay open so i can close it,delete it, destroy it and move on with life..hehehe BTW.. the database has been like this for about 15+ hours easily and i have since recreated the database with a different name now this one is in the way as i cant change the name or anything.
Please advise.
Thanks Again... D
DHeath
February 3, 2010 at 4:54 pm
Please run sp_who2 and see the process if something is in use
Also, query sysprocesses table
select * from sysprocesses
Just check by dbid and see if there any processes.
Or have you already done this?
M&M
February 3, 2010 at 5:06 pm
Thanks for the reply and yes i have done all the above...
fyi the dbid does not show up in sysprocess that would say something is holding it open.
-D-
DHeath
February 3, 2010 at 6:20 pm
Please try putting the database into emergency mode and then try to delete it.
UPDATE master..sysdatabases SET status=-32768 WHERE name='<dbname>'
Hope this should help.
M&M
February 3, 2010 at 6:33 pm
thanks again..when running the query that you have suggested this is returned
ad hoc updates to system catalogs are not allowed.
all suggestions are welcomed... i will keep trying and also since this is on a dmx domain (tied down) i cant use sqlcmd =[
-D-
DHeath
February 3, 2010 at 7:00 pm
How about:
restore database < your dbname here > with recovery;
Edit: No angle brackets, either.
February 3, 2010 at 7:06 pm
Thanks for the suggestion but when i try that this is the error..
restore database WSS_1 with recovery;
Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
=[
Thanks again any more ideas...Also no one has said why its not a good idea to just stop SQL and then delete the mdf/ldf files and then they are gone...what problems will this cause if i already have the data and not worried about losing anything?
-D-
DHeath
February 3, 2010 at 7:11 pm
DHeath (2/3/2010)
Thanks for the suggestion but when i try that this is the error..restore database WSS_1 with recovery;
Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
=[
Thanks again any more ideas...Also no one has said why its not a good idea to just stop SQL and then delete the mdf/ldf files and then they are gone...what problems will this cause if i already have the data and not worried about losing anything?
-D-
You didn't mention that this is possibly a sharepoint database. That could change things, as could this; is the database a mirror database?
February 3, 2010 at 7:12 pm
Also, do you have any snaphot databases on this database?
February 3, 2010 at 7:16 pm
Well i am in the process of building a new server...so moved the data in this particulare database already... as i mentioned earlier and a memory refresher is that i have already moved the data over and just need to get rid of this database. It is a sharepoint database..yes..
but the software is NOT on the server just yet. That is on the way so i need to get this gone if possible.
Also my apologies for not mentioning it being a sharepoint databse as i didnt think that would make any difference. oops
-D-
DHeath
February 3, 2010 at 7:19 pm
Have you shutdown the sharepoint services? If they are still running, that may be why you still get the error that the database is still in use.
February 3, 2010 at 7:22 pm
Also just to answer your question ..no snapshots.. yet..that too is something that will be set up later. But we do have the infrastructure already set and ready to rock as this database was taken from a server wehre all this is setup and i tried to do a SQL backup then take the .bak file and restore it and got myself in this bind..dang it lol
-D-
DHeath
February 3, 2010 at 7:27 pm
No sharepoint services on the server just yet.... but they will be once i pass it over to the Apps team.
Thanks for the insight and looking from a different angle.
-D-
DHeath
February 3, 2010 at 7:38 pm
Have to ask. Which database is stuck, the old one or the new one? How were you moving the database from the old server to the new server?
February 3, 2010 at 7:47 pm
Glad you asked..the first one (old)is stuck.. been stuck for over 15 hours minimal. The second (new)database is up and working. I tried to restore the first from a .bak file and it been in this state ever since.... when i tried the second one from a different .bak file that was derived from the same database as the first.. it worked like a champ. All thing were pretty much the same.. just 2 completely differnt outcomes. Hope this gives you a bit more insight
-D-
Thanks for the assistence Lynn much appreciated
DHeath
Viewing 15 posts - 1 through 15 (of 41 total)
You must be logged in to reply to this topic. Login to reply