(The Curious Case of… used to be part of our bi-weekly newsletter but we decided to make it a regular blog post instead so it can sometimes be more frequent. It covers something interesting one of us encountered when working with a client, doing some testing, or were asked in a random question from the community.)
I had a blog comment question a few days ago that asked why emergency-mode repair requires the database to be in EMERGENCY mode as well as SINGLE_USER mode.
All repair operations that DBCC CHECKDB (and related commands) performs require the database to be in single-user mode so there’s a guarantee that nothing can be changing while the checks and repairs are done. But that doesn’t change the behavior of what repair does – that needs emergency mode too.
Emergency mode is internally known as ‘bypass recovery’ mode, and is used when a database hasn’t had crash recovery run on it, because of corruption issues or a missing or damaged log file. It allows access to the database in the unrecovered state, which may mean the database has inconsistencies in its structures and/or data because of in-flight transactions that have not been rolled back (as recovery couldn’t run).
When a database is in emergency mode, it’s not possible to create a database snapshot (as that involves running recovery) and so a DBCC CHECKDB without a repair option simply treats the database as if it’s read-only, and runs the consistency checks. When a repair option *is* specified (and only REPAIR_ALLOW_DATA_LOSS is permitted) in emergency mode, that tells DBCC CHECKDB that the drastic, last-resort emergency mode repair should be performed. This will:
- Run as much crash recovery as possible, skipping errors, unlike real crash recovery which will fail when it encounters an error
- Delete the transaction log
- Create a new transaction log
- Run a regular REPAIR_ALLOW_DATA_LOSS check and repair
- Bring the database online if possible, albeit maybe with corruptions still in the database
So emergency mode isn’t single-user mode, and vice versa – they’re both required for an emergency-mode repair to run.
You can read more about emergency-mode repair in my blog post EMERGENCY-mode repair: the very, very last resort.
10 thoughts on “The Curious Case of… emergency-mode repair”
Marvelous post Indeed !
Sir, I upgraded 1 database from sql 2012 to sql 2017 and it restored successfully. When I ran dbcc checkdb, it failed with PFS corruption issue as mentioned in your 1 blog for sql 2014 PFS Corruption.
DBCC recommended repair allow data loss.
Here I dint use emergency mode SO will I always need Emergency mode for Repair plz?
PFS corruption and subsequently SGAM issue is still prevalent in sql 2017 also?
There’s no repair for PFS corruption I’m afraid, despite what CHECKDB says. You’ll need to export as much data as possible into a new database.
Respected Sir,
On Source database, multiple dbcc runs came clean and no issue reported in years. Still we need to be worried.
Thank you always
Msg 8948, Level 16, State 6, Line 5
Database error: Page (3:3863) is marked with the wrong type in PFS page (1:1). PFS status 0x40 expected 0x60
These errors were reported on Destination sql 2017 server as you mentioned in below article
https://www.sqlskills.com/blogs/paul/pfs-corruption-after-upgrading-from-sql-server-2014/
We also received the same error where no issue was reported on source server but threw sorts of these errors.
source server -sql 2012
destination server- 2017
Yes – you need to run repair to fix it.
Sir when I change the compatibility 2016 to 2018 I asked the customer to run CHECK DB to provided the errors
Table error: Object ID 565577053, index ID 1, partition ID 72057594086621184, alloc unit ID 72057594087800832 (type In-row data). Index node page (5:315083), slot 313 refers to child page (10:1885) and previous child (10:1884), but they were not encountered.
Msg 8980, Level 16, State 1, Line 1
Can you please help what is the next resolution for this….
They need to restore from backups, or run repair if no backups.
Hi Paul,
Thanks for the response.
But from customer point of view there is no issue with backups schedule or corruption they are getting warning errors emails for every 4 days or frequently we SQL team already given suggestions like and on some recommendation of same error bellow but on mdf files …
USE master
GO
ALTER DATABASE [TestTRNLogCorrupt] SET EMERGENCY
GO
ALTER DATABASE [TestTRNLogCorrupt] SET SINGLE_USER
GO
After that, we will try to execute the DBBC CHECKDB command using the REPAIR_ALLOW_DATA_LOSS option, in order to check the database for any inconsistency error and apply some special repairs to fix the Transaction Log corruption issue, as in the T-SQL script below:
DBCC CHECKDB ([TestTRNLogCorrupt], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
But the actual customer actual issue is with “ndf file’ when they tried to run DBCC CHECK DB they provided error saying
The entire solution which was given below is for transaction log file related only. But, the issue here in our case is with one of the ndf files. Please cross check the screen shot in your response for the error (attaching the same for your reference).
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x00000000; actual: 0x08200820). It occurred during a read of page (10:1337) in database ID 22 at offset 0000000000007200 in file ‘O:\.SQLData\NGAudit\ProdCore7.ndf’Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factor
Please go suggest on this and let us know if we are missing something here as this critical and need immediate assistance.
Thanks
Prudhvi
They need to restore from their backups.