SQL Server: After Restart, Database in “In Recovery” status, Can’t Access it

This is a known issue with SQL Server.

Here, I am sharing one situation when I have faced this issue and What are the possible causes to stuck SQL Server Database in “In Recovery” status/mode.

A few days ago, one of our Junior Database Developer was doing some activity in SQL Server 2012 Express Edition.
The Task was, To test the Index Rebuild on 6GB of Table.
Without knowing the SQL Server Version, He executed Index Rebuild on that Table and execution started.

SQL Server Express Edition can allocate only 10GB to each database.

The operation Index Rebuild require more disk space for rewriting the Table Indexes.
He was doing this exercise on VM of Azure Cloud and RAM is only 1 GB.
The operation was going on and size reached to max limit (10GB).

Now, SQL Server Engine had to raise one exception like “Does not sufficient space”, but before that It had to rollback the operation and It took a couple of minutes.
But meanwhile, Developer came to know that He executed Rebuild Index on SQL Server Express Edition, which may cause a problem of size because already Database size is 6GB.

Without checking anything, He cancelled the running execution of Rebuild Index Query.
The SQL Server Engine already started one process to Rollback the query, but now It has one explicit request to cancel the Query.

A Developer waited for a couple of minutes, but he felt that Database Server was stuck and He restarted the service of SQL Server.

Restarted SQL Server Service, This is one of the biggest mistakes that he did.

SQL Server was performing few transactions and he restarted SQL Server Service, which makes your database in “In Recovery” mode.

When he came back to SSMS and found that Database was in “In Recovery” mode and He was not able to access that database.

He presented the whole situation to me and was telling that Database might be crashed.

Is Database Crashed?

No, Internally SQL Server was restoring the database pending operations or transactions.
We should wait for a couple of minutes and automatically SQL Server will restore the database.

This happened because the developer restarted the service when SQL Server was performing a big transaction operation.
There can be also another reason like Transaction log file is very large.

How to check, SQL Server weather Restoring Database or Not?

You can check recent error log file of SQL Server, where you can find recent log like below messages.
You can find some progress (%) which SQL Server actually restoring your database so wait for a couple of minutes.

Please share your ideas and opinions about this topic with me, your contribution will add true value to this topic.
If anyone has doubts on this topic then please do let me know by leaving comments or send me an email.

If you like this post, then please share it with others.
Please follow dbrnd.com, I will share my experience towards the success of Database Research and Development Activity.

I put up a post every day, please keep reading and learning.
Discover Yourself, Happy Blogging !
Anvesh M. Patel.

More from dbrnd.com

Leave a Reply

Be the First to Comment!

Notify of