Help!! Database always In Recovery...

Hi all,

I had to change the path of .mdf and .ldf files, so I decided to:

1) Take offline the database

2) run the quey ALTER DATABASE... MODIFY to change the path

3) Bring online the database.

The last step hung up (with no errors) and left the database In Recovery.

When I tried to stop and restart sql server other databases changed their status In Recovery...

Here is a dump of Errorlog files

2007-08-26 18:13:29.28 spid24s Starting up database 'DbOrdini'.

...

...

2007-08-26 18:13:30.09 spid24s * BEGIN STACK DUMP:
2007-08-26 18:13:30.09 spid24s * 08/26/07 18:13:30 spid 24
2007-08-26 18:13:30.09 spid24s *
2007-08-26 18:13:30.09 spid24s * Location: "logmgr.cpp":5334
2007-08-26 18:13:30.09 spid24s * Expression: !(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)
2007-08-26 18:13:30.09 spid24s * SPID: 24
2007-08-26 18:13:30.09 spid24s * Process ID: 1380
.....

.....

2007-08-26 18:13:30.40 spid24s Error: 17066, Severity: 16, State: 1.
2007-08-26 18:13:30.40 spid24s SQL Server Assertion: File: <"logmgr.cpp">, line=5334 Failed Assertion = '!(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
2007-08-26 18:13:30.40 spid24s Error: 3624, Severity: 20, State: 1.
2007-08-26 18:13:30.40 spid24s A system assertion check has failed. Check the SQL Server error log for details

Could it be dangerous trying to kill this process ? If not, what is the best way do to it ? From Sql Server Activity Monitor (spid 24) or from Task Manager ?

Thanks in advance

you should have used Detach /attach method in this scenario. I hope u have kept backup.

(a) Detach db first

(b) copy the files to whereever u want

(c) attach the db

this should work

|||

Thanks for your answer!!

I have a backup, but I can't restore it. If I try to detach the database I get this error:

Cannot detach the database .. because it is currently in use (Sql Server Error 3703).

I can't do anything about this database, I tried to drop it, to open in emergency mode,...but nothing to do.

If I kill the process from Task Manager, do you think I colud damage other databases ?

Thanks

|||

You can't detach database when somebody is connected to it. Kill the process it will not harm... But i repeat again before doing anything on database you should have backup. Its dba mantra... Use SP_Who and see who all are connected to it kill the process and detach and attach.

otherwise you can restore the datbase with some other name using WITH MOVE option.

Madhu

|||

I have a database backup, but I would like to detach the db hanging in recovery, because when I stop and restart Sql Server other databases change their status in recovering...

I executed sp_who and sp_who2 and I get a strange result: the spid 24 (the id I get from error log files) is referred to a different database. Here is the row

CPU TIME

24 BACKGROUND sa . otherdb UNKNOWN TOKEN 13368187 43 01/01 00:00:00

To fix this problem do I have to kill the spid 24, and then detach and attach the db ?

Thanks again

|||

since the spid points to a different db as suggested by Madhu, you can try to detach it now and move the files to diff location and then attach...else try to drop the database and restore from latest backup......if nothing works try to brig the db into emergency mode as below and export the data out to a diff db

alter database dbname set emergency........may be now this command might work as there is no exclusive access to the db........

|||

I have tried to detach the database, but there's no hope, I get the message

Msg 6107, Level 14, State 1, Line 1
Only user processes can be killed.

and ALTER DATABASE dbname SET Emergency returns

User does not have permission to alter database dbname or the database does not exist.

SpId24 is a system process, so I can't kill from Management Studio.

Thanks for your replies

|||

try this...

(a) Stop the service

(c) Copy the MDF and LDF file to someother location

(d) Delete the mdf and LDF file from the current location

(e) restart the service

(f) Attach the MDF & LDF file back to server

Madhu

|||

Great!! It's worked!!!

Thanks all for the support