CODEWITHSUNDEEP
sql-server-2012recoverydisaster-recovery
abianari
abianari

Reputation: 353

sql server 2012 go to recovery mode take long time afte reboot

After rebooting server one of the database goes to mode In recovery . The log size is 117 Gig )

When I looked at errorlo the last message was:

2015-03-11 11:27:43.04 spid36s Recovery of database 'XXXx_DW' (10) is 3% complete (approximately 114050 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.

The only solution is waiting..? What happen if I restore latest backup to database, it will go online or not?. Any suggestion appreciated

Upvotes: 1

Views: 3241

Answers (2)

user13915077
user13915077

Reputation: 1

If you are stuck in "In Recovery mode" from so long time then you need to take below steps:

Note: Before performing below steps you need to have database back up upto the mark then and only then you go for below steps.

  1. Go to the physical location of database.. i.e my database name is Optimistic.mdf & Optimistic.ldf check it's date and time.

  2. If it found ok then go to the mssql services and stop your instance service.. i.e instance name is default->MSSQL or for named instance->let's consider robocom(my instance name your instance name will be different so please check). Now stop that service.

  3. Copy database from physical location and paste it to some other location like out of folder or some other drive. In my case I copied all 2 mdf and ldf file of problematic databases into out of the folder (Because folder location is already associated with sql server. So it will remain safe.

  4. Now again go to the physical location where already problematic database is exist and delete both mdf & ldf files one by one.

  5. Refresh that folder and cut databases that you keep safe in another location and paste it to the deleted location.

  6. Start Sql Service that you stopped.

  7. Start SQL Management studio and refresh it.

Hope your "in recovery" mode problem will solve... Only apply this solution if you are stuck in recovery mode for so long time.. Thank you...

Upvotes: 0

Shanky
Shanky

Reputation: 626

First I would like to ask when you rebooted SQL Server did you made sure no job was running, no long running transactions were there, no user was running some transaction ?

If you missed above point I would say you are not aware about how to shutdown SQL server, specially the production one.

When you shutdown SQL Server all inflight transactions would be stopped/cancelled and when database comes online SQL Server crash recovery would happen and that is what happening in message

Recovery of database 'XXXx_DW' (10) is 3% complete

Recovery has three phases analysis, redo and undo. Database is completely accessible only after all three phases of recovery has completed. In fast recovery, which is enterprise feature, database comes online after REDO phase. Yes your only solution is waiting. All these phases are required to bring database to consistent state, unless it finds itself in consistent state database won't come online

What happen if I restore latest backup to database

At this point of time when database is in recovery you won't be allowed to perform restore on it as it is locked and wont allow access. You can restore the backup with diferent name though but i dont think there is any advantage in doing this.

Moral of story: Dont just blindly reboot server.

Upvotes: 2

Related Questions