SQLTeam.com | Weblogs | Forums

Database recovery mode


#1

my database is now in recovery mode

how to do

I stopped the service but nothing happened

thank you


#2

Did you recently perform a database restore? Depending on your use of the RECOVERY or NORECOVERY options, the database is either ready to go or is waiting for additional restore files (Differential and/or Log).


#3

no I have not performed a restore operation

when I click on the right bouton, the base property, it shows me:
BSIAZUR the database is inaccessible. (ObjectExplorer)

BSIAZUR is the name of my database


#4

If you query sys.databases, what's the value for state_desc for this database? Recovering or restoring?


#5

Thank you for your help
I answer you tomorrow because I have no access to the server

on the Explorer of database the exact message is:
pending recovery in frensh: Récupération en attente

NB:
what exactly happened on the server:

  • A connection problem
  • a server restart

I'm sorry for my english


#6

Here's what I'm thinking. With the server restart, the databases need to recover by rolling back any transactions that didn't complete.I have run into a situation where some of the resources used by a database, in my case it was disks, did not come back online after the reboot. This leads to a database that can't perform its "recovery". Once we resolved the underlying disk issue, the databases completed their internal validation and came back online.


#7

<< Once we resolved the underlying disk issue, the databases completed their internal validation and came back online. >>
I didn't understand the meaning of this sentence and especially the "underlying disk issue" , i didn't reconized his principal function !


#8

Did it go into this mode after a reboot? If so, you likely have a VLF issue. What does the SQL Server Error Log for the recovery information? If it's actively recovering, it should give you updates on which phase it's in and the percentage.


#9

if i shootdown the serve SQL can i attach the files ( MDF / LDF ) in another server ???
Thanks


#10

If it's due to VLFs, it's going to happen on another server. Check the Error Log to figure out what's going on.


#11

i ll try to fix this problem and if i have a solution i will tell you if not , you may help me again
thanks !


#12

I had a disk problem that prevented my databases from starting their "recovery" processing. I had to fix my disk problem first. Once I did that, the databases were able to quickly complete their recovery and came back online. I mentioned this happened to suggest that you investigate if all of the drives you are using are indeed online and ready.


#13

I've had that disk problems numerous times. Drives weren't coming online before SQL Server started in some cases. Other times, drives would not appear after a reboot. In all cases, the server admin had to fix it. Luckily this issue happened in non-prod more often than in prod, but what a nuisance.


#14

On some servers we have SQL's Services not set to automatically start, and have a BATch (etc) file to start them after a short delay - allowing for other servers / equipment etc. to have time to start after some twit turns off the servers instead of the lights!!


#15

the state of mydatabase is RECOVERY_PENDING


#16

i query this procedure and the database become in mode emergency

CREATE PROCEDURE RECOVERY_SP AS
declare @dbname varchar(255);
DECLARE dbname_cursor CURSOR FOR
SELECT name--, database_id, create_date, STATE_DESC
FROM sys.databases
WHERE name = 'BSIAZUR01' --STATE_DESC in ('SINGLE_USER', 'SUSPECT', 'RECOVERY_PENDING');

OPEN dbname_cursor
FETCH NEXT FROM dbname_cursor
INTO @dbname

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Recovering "' + @dbname + '"...'

exec sp_resetstatus @dbname;
exec('alter database [' + @dbname + '] set emergency');
dbcc checkdb(@dbname)
dbcc checkdb(@dbname)
exec('alter database [' + @dbname + '] set single_user with rollback immediate');
dbcc checkdb (@dbname, repair_allow_data_loss)
exec('alter database [' + @dbname + '] set multi_user');
dbcc checkdb (@dbname)

FETCH NEXT FROM dbname_cursor 
INTO @dbname

END
CLOSE dbname_cursor;
DEALLOCATE dbname_cursor;


#17

You can actually do that with the service and not have to bother with a batch file. The service has a delay option. We had to use that option on our Cisco servers. Every single one of them had that problem.


#18

Why did you run that script? What are you trying to resolve with that?

Bring it back online with this:
ALTER DATABASE dbnamegoeshere SET online


#19

Didn't know that, thanks Tara.


#20

Take a look at this article hope this will help you out: http://blog.sqlauthority.com/2015/03/04/sql-server-database-stuck-in-in-recovery-mode-after-restart/