July 18, 2017 at 12:28 am
Hi hope someone can assist with this.
We have a database running on SQL 2008 in our production environment which recently appeared to be offline . From the client side it gave an error saying that the database is in recovery mode but from the server you could still query it but you could not expand the database. I manually took it offline and brought it back online again which fixed the problem.
However I went through the SQL and Server logs and could not find anything that was logged.
Has anyone else experienced this before and if so how did you find the root cause?
The database is in a cluster running on windows server 2012.
July 18, 2017 at 1:19 am
I had a similar issue many years ago running on a 2008r2 express instance, a week later the master became corrupt in some manor and ended up rebuilding the server.
only thing i could recommend (baring in mind im a dev not a DBA) would be a repair on the SQL instance and hope it corrects any underlying issues that may of caused the connectivity issues.
theres always a chance that the native client just had an identity crisis and span around in circles screaming 🙂
July 18, 2017 at 4:35 am
Recovery means it's completing the last steps of a startup, a failover, or a restore. Did anything like that happen around the database? It could be that you merely had a long running recovery, came along just as it was finishing, and did the offline/online for no good purpose. It's hard to say after the fact exactly what it might be. I'd suggest querying running processes next time using sys.dm_exec_requests to see if there are blocked processes. I'd also do a consistency check through DBCC on the database. Validate that you have good backups in place by testing a restore.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 18, 2017 at 5:58 am
Grant Fritchey - Tuesday, July 18, 2017 4:35 AMRecovery means it's completing the last steps of a startup, a failover, or a restore. Did anything like that happen around the database? It could be that you merely had a long running recovery, came along just as it was finishing, and did the offline/online for no good purpose. It's hard to say after the fact exactly what it might be. I'd suggest querying running processes next time using sys.dm_exec_requests to see if there are blocked processes. I'd also do a consistency check through DBCC on the database. Validate that you have good backups in place by testing a restore.
Thanks for the reply I will have a look if it happens again.
Not sure why it would have been doing a recovery as all that was setup on the server is a maintenance plan to do backups. No failover, restore or startup happened.
July 18, 2017 at 7:35 am
wium - Tuesday, July 18, 2017 5:58 AMThanks for the reply I will have a look if it happens again.
Not sure why it would have been doing a recovery as all that was setup on the server is a maintenance plan to do backups. No failover, restore or startup happened.
Something happened if the database was actually in recovery. Now, it's also possible that some code had poorly constructed failure logic. It hit a timeout or something else and the error trapping had this message in it for some reason (yeah, I think that's a weird possibility, but it is a possibility). If you didn't see the database within SSMS or a query as marked "In Recovery", then it might not have been.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 18, 2017 at 7:41 am
Grant Fritchey - Tuesday, July 18, 2017 7:35 AMwium - Tuesday, July 18, 2017 5:58 AMThanks for the reply I will have a look if it happens again.
Not sure why it would have been doing a recovery as all that was setup on the server is a maintenance plan to do backups. No failover, restore or startup happened.
Something happened if the database was actually in recovery. Now, it's also possible that some code had poorly constructed failure logic. It hit a timeout or something else and the error trapping had this message in it for some reason (yeah, I think that's a weird possibility, but it is a possibility). If you didn't see the database within SSMS or a query as marked "In Recovery", then it might not have been.
When I logged onto the server I could see the database and could do a 'select * from sysfiles' however I could not expand it. The thing that gets me is that nothing was logged in SQL or even the server logs.
July 18, 2017 at 8:24 am
wium - Tuesday, July 18, 2017 7:41 AMWhen I logged onto the server I could see the database and could do a 'select * from sysfiles' however I could not expand it. The thing that gets me is that nothing was logged in SQL or even the server logs.
Hmmmm.... Hard to say without more evidence. You connected to the database directly and queried it? That means it was online. Not being able to expand it from within SSMS suggests blocking or some other resource issue. If the database was in recovery, normally, it's marked that way within SSMS.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 19, 2017 at 12:21 am
Grant Fritchey - Tuesday, July 18, 2017 8:24 AMwium - Tuesday, July 18, 2017 7:41 AMWhen I logged onto the server I could see the database and could do a 'select * from sysfiles' however I could not expand it. The thing that gets me is that nothing was logged in SQL or even the server logs.
Hmmmm.... Hard to say without more evidence. You connected to the database directly and queried it? That means it was online. Not being able to expand it from within SSMS suggests blocking or some other resource issue. If the database was in recovery, normally, it's marked that way within SSMS.
Yes when logging on via the host server I could querie but not expand from the client it gave the recovery error... This is what got me as it gave no errors and nothing is logged! I will just have to continue to monitor the status of the db from now on.
July 19, 2017 at 1:13 am
Is it still unavailable?
If you query sys.database, what's the exact value for State_desc for that database?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 27, 2017 at 4:45 am
Looks like some client-side issue . Irrespective of the status in SSMS I suggest to query from the client and see the response . If it still says that the database is in recovery contrary to the outcome while running from the server , it should be a client's data-refresh issue.
Thank you
Arshad
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply