So I had a corruption issue and I was thinking about running repair but I wanted to know what would potentially get deleted.
I was faced with the following message.
Table error: Object ID 1189579276, index ID 1, partition ID 72057594046578688, alloc unit ID 72057594053132288 (type In-row data). Page (1:441). CHECKDB found 0 allocation errors and 4 consistency errors in table ‘Person.EmailAddress’ (object ID 1189579276). CHECKDB found 0 allocation errors and 4 consistency errors in database ‘AdventureWorks2014’. Repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB
I created the above using a nice command, if you know what I mean! Anyways, try running a select statement.
SELECT * FROM [AdventureWorks2014].[person].[EmailAddress]
Msg 824, Level 24, State 2, Line 2: SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x269704db; actual: 0xd9687b24). It occurred during a read of page (1:440) in database ID 5 at offset 0x00000000370000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\AdventureWorks2014_Data.mdf’
Before we start looking further this is what the table looked like BEFORE the issue.
I am curious to find out what will actually get deleted when using the REPAIR_ALLOW_DATA_LOSS option.
Did you know that you can wrap CHECKDB in a transaction and “take a look”?
ALTER DATABASE [AdventureWorks2014] SET SINGLE_USER WITH ROLLBACK IMMEDIATE BEGIN TRANSACTION DBCC CHECKDB ('AdventureWorks2014', REPAIR_ALLOW_DATA_LOSS)
CHECKDB fixed 0 allocation errors and 4 consistency errors in database ‘AdventureWorks2014’.
--CHECK DODGY TABLE SELECT * FROM [AdventureWorks2014].[person].[EmailAddress]
The question is if that is serious amount of data loss? I think so! Lets’ rollback.
-- Don't like? ROLLBACK TRANSACTION SELECT * FROM [AdventureWorks2014].[person].[EmailAddress]
Back to square one – Msg 824, Level 24, State 2, Line 38 SQL Server detected a logical consistency-based I/O error etc.
What did the repair do? Let’s check the page with the issue.
DBCC TRACEON (3604) DBCC PAGE (N'AdventureWorks2014',1,440,3);
Last entry is below:
Slot 82 Column 1 Offset 0x4 Length 4 Length (physical) 4
BusinessEntityID = 83
Let’s look at the next page 441. The first entry is ID 84.
DBCC TRACEON (3604) DBCC PAGE (N'AdventureWorks2014',1,441,3);
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4
BusinessEntityID = 84
Slot 81 Column 1 Offset 0x4 Length 4 Length (physical) 4
BusinessEntityID = 165
So it looks like repair removed Ids 1- 83 which lived on page 440. Check the suspect table and if you see event_type 7 it means that the page (440) had been de-allocated which happened after I committed the above transaction so you can see how it all matches up.
use msdb go select * from suspect_pages where [page_id] = 440
Time to find those things called backups.