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.

Filed under: Admin, Backup & Recovery, CHECKDB, Corruption, SQL SERVER Tagged: Corruption, DBCC CHECKDB, Internals, Repair, SQL server, TSQL