Week 6 Challenge Details

 

You can download a zip file with a single full backup file created SQL Server 2005 that can be restored and used on SQL 2008, SQL Server 2008R2, SQL Server 2012 or SQL Server 2014. I have confirmed it on SQL Server 2005, and SQL Server 2014.

The story, corruption was encountered running DBCC CheckDB that looked like this:

Msg 8938, Level 16, State 2, Line 1

Table error: Page (1:1849), Object ID 2073058421, index ID 2, partition ID 72057594038386688, alloc unit  ID 72057594043367424 (type In-row data). Unexpected page type 1.

Msg 8976, Level 16, State 1, Line 1

Table error: Object ID 2073058421, index ID 2, partition ID 72057594038386688, alloc unit ID 72057594043367424  (type In-row data). Page (1:1849) was not seen in the scan although its parent (1:1832) and previous (1:1848)  refer to it. Check any previous errors.

Msg 8978, Level 16, State 1, Line 1

Table error: Object ID 2073058421, index ID 2, partition ID 72057594038386688, alloc unit ID 72057594043367424 (type In-row data). Page (1:1850) is missing a reference from previous page (1:1849). Possible chain linkage problem.

CHECKDB found 0 allocation errors and 3 consistency errors in table 'Customers' (object ID 2073058421).

CHECKDB found 0 allocation errors and 3 consistency errors in database 'CorruptionChallenge6'.

repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (CorruptionChallenge6).

 

Someone had disabled the SQL Server Agent, so there were no recent automated backups run on this database.  After the corruption was encountered, a backup was performed in order to distribute the corrupt database for the competition. There were no transaction log backups, only the single full database backup that contains the corruption.

You may be tempted to just drop and recreate an index and call it done. Do you really think I would make it that easy, this is the Database Corruption Challenge.

How to know if you succeed

When you think you have success, run the following query and send me the results, along with the usual:

• Detailed steps with what you did to complete the challenge.
• Proof that you removed the corruption, and that you didn’t lose any data.

SELECT COUNT(1) AS check1,
       COUNT(DISTINCT FirstName) AS check2,
       COUNT(DISTINCT MiddleName) AS check3,
       COUNT(DISTINCT LastName) AS check4,
       CHECKSUM_AGG(CHECKSUM(FirstName)) AS check5,
       CHECKSUM_AGG(CHECKSUM(MiddleName)) AS check6,
       CHECKSUM_AGG(CHECKSUM(LastName)) AS check7
  FROM [Customers];

 

The first person to submit a winning solution with all this info will receive 2 points in the challenge; all those who submit correct answers who are not first will receive 1 point.

There is a bonus point available this week for adding a comment to the DBCC CheckDB speed blog post. All who answer the question of what you do when CheckDB is slow and post it as a response to the blog, will earn a bonus point.

To Submit Your Results.

Mail your results to  before Tuesday May 26^th, 2015 at 5:00pm pacific time.

 

Extra Clue

If you are having difficult and need an extra clue, there is an extra clue available. Don’t view the extra clue unless you really need it. The extra clue was added on May 25th at 1:30pm (pacific time).

File Download

The zip file contains a single full backup. See the challenge details for a description.

CorruptionChallenge6.zip

Related Links

• Current Scores
• Newsletter Sign up
• Database Corruption Challenge T-Shirt
• Week 1, Week 2, Week 3, Week 4, Week 5
• Slow DBCC CheckDB
• Database Corruption Worksheet

Database Corruption Challenge Sponsored by

Related Links:

• REPAIR_ALLOW_DATA_LOSS example
• Corruption Repair Help from Stedman Solutions.
• Contact us if you need help. https://Stedman.us/30

Getting Help from Steve and the Team

Contact us for your Free 30 minute consultation OR
schedule your appointment now.