January 6, 2018 at 7:13 am
Had an issue recently where I found a database in Suspect mode. No access was possible.
Started to look through logs and saw the offending action (SP with large data movement filled the log drive).
For the life of me I couldn't access the db, I tried ALTER DATABASE [db] SET EMERGENCY;GOALTER DATABASE [db] SET SINGLE_USER;
No access was allowed from SSMS or SQLCMD (at least for me).
My initial thought was restore from backups. So off I went to try and detach db so I could move the filegroups to an offline area. I needed to restore same filegroups you see.
Couldn't figure out how to detach the db in the state it was in. I wanted to keep the original files.
I ended up restarting SQL Server after trying to use SQLCMD to connect. Upon re-start the recovery of the DB kicked in automatically.
From the logs : Recovery of database 'db name' (11) is 99% complete (approximately 20 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
Is this same action as a DBCC CHECKDB?
Once this finished it gave me back the database.
I was able to remove offending data and shrink files.
In this case there was no data loss (staging db) and schema was okay too. I had backups ready just in case.
I was able to remove offending data and shrink files.In this case there was no data loss and schema was okay too.
I had backups ready just in case.
It turned out okay in the end but I want to learn a little more about the auto recovery. Is it normal to wait for Recovery before restoring the db? I suppose SQL is completing the things it couldn't do. If there was data/schema loss after Recovery I suppose I could have then done a Restore - how could I have done a Restore without first doing a Recovery? I had to wait around 2 hours for Recovery to finish which in this case was okay(ish) but I can imagine situations where you want to kick off the Restore straight away. Also - I wonder how the Recovery was possible with the full log drive. Don't get me wrong - SQL was very good even when things went pear shaped. Will definitely be trying to corrupt some test db's 🙂 Is there a way to simulate storage issues, apart from unplugging drives? I suppose I could hack a VM 🙂
Sorry for the long post - I see things like this as great way to learn 🙂
January 6, 2018 at 7:52 am
First suggestion would be to look through Paul Randal's website, you'll find plenty to read there on the subject
😎
January 7, 2018 at 10:24 am
Few things.
If a full log caused a suspect DB, then you've hit a bug where there was insufficient log reservation. Please report that to Microsoft with a repo if possible.
Normally a full log will not cause a suspect DB.
Recovery is not the same as a CheckDB
Given that a restart let you access the DB again, it probably was not SUSPECT (the value for state_desc in sys.databases), but rather RECOVERY_PENDING, which has similar symptoms but is usually less of a problem as it can often be fixed by a restart of the DB or instance. If a DB is suspect, a restart will do nothing other than waste time.
Recovery is a normal process that runs every time you restart SQL or restore a database (after the restore has finished the data movement portion)
Do not ever detach a suspect DB. They don't re-attach.
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
January 9, 2018 at 3:29 am
The database had (Suspect) after the db name in SSMS. After re-start it went into Recovery Pending State and I could see in the logs the recovery was underway.
Due to this being a staging db it gets used many times over night - I think it's this repetition that put the db into Suspect mode.
I suppose I could have dropped the db if I wanted to kick off the back straight away.
Do you include recovery time into DR plans (RTO)? Probably best to allow db to complete recovery. I always thought a dba would restore as soon as the db was found to be suspect.
January 9, 2018 at 3:38 am
leehbi - Tuesday, January 9, 2018 3:29 AMThe database had (Suspect) after the db name in SSMS. After re-start it went into Recovery Pending State and I could see in the logs the recovery was underway.
Could have been SSMS flagging both recovery_pending and suspect the same way. Checking the State_desc in sys.databases (and the error logs) is the best way to determine state
Due to this being a staging db it gets used many times over night - I think it's this repetition that put the db into Suspect mode.
That will not cause a DB to go suspect
Probably best to allow db to complete recovery.
Databases are not accessible while recovering, so you don't really have a choice.
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
January 9, 2018 at 6:05 am
I remember trying to set Emergency Mode but it wouldn't let me do this. I can understand SQL kind of had pants down and wanted to pull them up before letting me near the DB.
This just shows that DR really does need to be tested for the different failure paths.
January 18, 2018 at 6:26 am
This was removed by the editor as SPAM
January 18, 2018 at 9:07 am
no need to set single_user, correct command would be
ALTER DATABASE [db] SET EMERGENCY;GO
USE [bd]
GO
DBCC CHECKDB(0) WITH ALL_ERRORMSGS, NO_INFOMSGS
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply