This article is half-done without your Comment! *** Please share your thoughts via Comment ***
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'db_rnd'. 2016-10-06 00:03:26.85 spid16s Recovery of database 'db_rnd' (5) is 4% complete (approximately 570 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 2016-10-06 00:03:27.74 Logon Error: 18456, Severity: 14, State: 38. 2016-10-06 00:03:27.74 Logon Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'db_rnd'. 2016-10-06 00:03:29.76 Logon Error: 18456, Severity: 14, State: 38. 2016-10-06 00:03:29.76 Logon Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'db_rnd'. 2016-10-06 00:03:31.01 spid16s Recovery of database 'db_rnd' (5) is 5% complete (approximately 530 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 2016-10-06 00:03:31.96 Logon Error: 18456, Severity: 14, State: 38. 2016-10-06 00:03:31.96 Logon Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'db_rnd'. 2016-10-06 00:03:33.98 Logon Error: 18456, Severity: 14, State: 38. 2016-10-06 00:03:33.98 Logon Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'db_rnd'. 2016-10-06 00:03:34.62 spid16s Recovery of database 'db_rnd' (5) is 6% complete (approximately 494 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 2016-10-06 00:03:36.38 Logon Error: 18456, Severity: 14, State: 38. 2016-10-06 00:03:36.38 Logon Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'db_rnd'. 2016-10-06 00:03:38.39 Logon Error: 18456, Severity: 14, State: 38. 2016-10-06 00:03:38.39 Logon Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'db_rnd'. 2016-10-06 00:03:38.54 spid16s Recovery of database 'db_rnd' (5) is 7% complete (approximately 471 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 2016-10-06 00:03:41.23 Logon Error: 18456, Severity: 14, State: 38. 2016-10-06 00:03:41.23 Logon Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'db_rnd'. 2016-10-06 00:03:42.35 spid16s Recovery of database 'db_rnd' (5) is 8% complete (approximately 451 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 2016-10-06 00:03:43.27 Logon Error: 18456, Severity: 14, State: 38. 2016-10-06 00:03:43.27 Logon Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'db_rnd'. 2016-10-06 00:03:46.29 Logon Error: 18456, Severity: 14, State: 38. 2016-10-06 00:03:46.29 Logon Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'db_rnd'. 2016-10-06 00:03:46.58 spid16s Recovery of database 'db_rnd' (5) is 9% complete (approximately 440 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 2016-10-06 00:03:48.32 Logon Error: 18456, Severity: 14, State: 38. 2016-10-06 00:03:48.32 Logon Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'db_rnd'. 2016-10-06 00:03:50.60 spid16s Recovery of database 'db_rnd' (5) is 10% complete (approximately 427 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. 2016-10-06 00:03:51.43 Logon Error: 18456, Severity: 14, State: 38. 2016-10-06 00:03:51.43 Logon Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'db_rnd'. 2016-10-06 00:03:53.63 Logon Error: 18456, Severity: 14, State: 38. 2016-10-06 00:03:53.63 Logon Login failed for user 'sa'. Reason: Failed to open the explicitly specified database 'db_rnd'. 2016-10-06 00:03:55.38 spid16s Recovery of database 'db_rnd' (5) is 11% complete (approximately 423 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required. |
Leave a Reply