my database is now in recovery mode
how to do
I stopped the service but nothing happened
thank you
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).
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
If you query sys.databases, what's the value for state_desc for this database? Recovering or restoring?
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
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.
<< 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 !
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.
if i shootdown the serve SQL can i attach the files ( MDF / LDF ) in another server ???
Thanks
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.
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 !
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.
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.
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!!
the state of mydatabase is RECOVERY_PENDING
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;
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.
1 Like
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
Didn't know that, thanks Tara.
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/
1 Like