Start Free Trial

asked on

database in recovery

yesterday i recieved a .mdf and .ldf file of a sql server database that we are now to host. when i attached it it says that it is in recovery mode. this is the first problem but the major problem is when i came in this morning one of the other databases is not telling me that that is in recovery as well

can anyone tell me how to get my major db out of recovery and what on earth is going on

one last thing its sql server 2005

thanks

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

got the following error message

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.

ASKER

ive just looked in the activity monitor and there doesnt appear to be any connections to my db

Guy Hengel [angelIII / a3]

you must connect to "master" database to start the recovery ...
apart from that: can you check in the ERRORLOG of the instance if you see anything that could be related?

ASKER

hi thanks for your help

ive just run that query on the master db and got the same error. im looking in the error logs now and there seems to be loads of these

Message
Process 15:0:0 (0x850) Worker 0x03D820E8 appears to be non-yielding on Scheduler 1. Thread creation time: 12915054844140. Approx Thread CPU Used: kernel 0 ms, user 15 ms. Process Utilization 0%. System Idle 99%. Interval: 50895296 ms.

could this be related?

Please check the error log and find out what could have caused this.
Check all entries in the error log related to the database name and restore/recovery/attachment.

If you have access to the original db files, I suggest that you detach this one, move the files elsewhere and then attach a fresh copy of DB files from the old server.

It's possible that they are corrupted as you copied them on to the new server.
Check your disk from the command prompt using CHKDSK DRIVE_LETTER:

ASKER

ive just checked and there are another 3 db's that are now in recovery, luckily ive replicated them with a slightly different name from lastnights backup. ive just tried to detach one of the databases and i get the error

cannot detach the database 'dbname' because it is currently in use (microsoft sql server, error: 3703)

ASKER

just out of interest all the processes that are running are sleeping apart from one the sa which is running on the tempdb

i cant understand why this should be running on there will it be ok if i kill this and other processes then try and run that command again???

Guy Hengel [angelIII / a3]

>cannot detach the database 'dbname' because it is currently in use (microsoft sql server, error: 3703)
usually, this is just the "GUI" of the enterprise manager/sql managmenet studio that, by selecting the database in the browser, there is a connection with that database as context, which makes the database cannot be restored or detached.

ASKER

that sounds about right, how do i get around that problem

ASKER

ive just run this sql

USE Master
ALTER DATABASE TestTheNations SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE TestTheNations WITH RECOVERY

and recieved the following error

Msg 5011, Level 14, State 7, Line 2
User does not have permission to alter database 'TestTheNations' or the database does not exist.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.
Msg 3101, Level 16, State 1, Line 3
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

DBAduck - Ben Miller

What this tells me is that SQL Server itself is not done with the recovery process that always happens before the SQL Server allows access to any database. So without this recovery process finishing (this happens on SQL Server startup as well as at attach time or restore time) the SQL Server will be the only one with access to it.

You can look in the SQL Server error logs and find the place where it starts recovering that database and follow the chain of events after that.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Hi Pastorchris

thanks for that, however im still getting pretty much the same error which seems to start with telling me that i dont have permission to alter the database or it doesnt exist.

its been sat there for two days now in recovery mode, is there no way i can just delete it, what if i restart the actual server??

i could really do to just clean this up one way or another

thanks

Hi Andrew,
I suggest that you do a cleanup.
To delete a database which is bears the status "recovering", just use SSMS and delete it.
I suggest that you re-do the copying of files from the original source and then re-attach.

You could as well do a full backup of the original database and restore the database this side.

Try those two options and let me know how it goes Andrew.

ASKER

Hi chris couple of things, not to sure if they are relavent

1. tried to delete the db in SSMS and I got the same error "cannot drop database 'testthenations' because it is currently in use"

2. ive just been told that the orriginal .mdf file came from SQL Server Express Edition, they have just sent me a .bak file of the same database which i tried to restore and I got the error "the media family on device 'path to .bak file' is incorrectly formed..........

what ever was in the original .mdf file seems to still be in the .bak file

ASKER

Hi all

ive just found out that the database was created using server 2008, does anyone know if there is an issues trying to install it into server 2005?

ASKER

http://www.devx.com/dbzone/Article/40531/0/page/1

i think i have found the reason im having these problems

DBAduck - Ben Miller

If the database originated in SQL 2008, you cannot backup from there and restore it to SQL 2005. This is why you get the error that the backup is not in the correct format.

You can transfer objects using SSIS and get the objects to copy from the 2008 to 2005 database, but you have to have access to both systems because you will be copying from live to your 2005. That way you can get the stuff over. But plain old backup and restore will not work for you in this one.

ASKER

hi thanks for that, what about if I run the Generate SQL Server Scripts for 2005 when in 2008 according to the article ive mentioned above I should then be able to import it into 2005?

DBAduck - Ben Miller

well you can, but that will not get you the data. but you will get the objects.

you could then transfer the data with import/export in SQL.

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Remember first to test all your scripts extracts from SQL 2008 database on the SQL 2005 instance for compatibility before you run them.
These of course include views, stored procedures, triggers and functions.

All the best.

ASKER

thanks ill let you know how i get on

ASKER

Hi quick update i followed this tutorial and it seemed to work

http://www.devx.com/dbzone/Article/40531/0/page/1

thanks

Andrew

ASKER

ok folks this is my final solution that seems to have sorted it. my problem came to a head last night when the server kept crashing and db after db started going into recovery.

my solution. i stopped the server, deleted the original 2008 db that was still in recovery and then restarted the server.

all now and im preying is working ok

word of warning ALWAYS CHECK THE VERSION OF A SUPPLIED DB AND DO NOT EVER TRY AND PUT A 2008 DB INTO 2005

@Andrew,
That makes my last 3 posts equal to the solution so please assign the points to me :-)

ASKER

ha well thats a first.

anyway as for the points, thanks for your help and your welcome to them

ASKER

most sorted out myself