Skip to content

When Corruption Strikes – Online Presentation Wed May 13th

On Wednesday May 13th I will be presenting to the PASS DBA Virtual Chapter Meeting, at Noon Mountain time (convert to your time zone).

The session is titled “When Database Corruption Strikes“, and the event has been sponsored by Dell Software

Abstract:

You are working along month after month with no problems in your database. Suddenly someone reports that their query won’t run. They get an error stating “SQL Server detected a logical consistency-based I/O error”, or something even scarier. Do you know what to do now? We will walk through 3 or 4 actual corrupt databases exploring ways to go about finding and fixing the corruption. More importantly we will explore how to prevent further data loss at the time corruption occurs. Learn what things you should do to protect yourself when corruption strikes. Learn what to avoid that will make things worse. You will leave with a checklist of steps to take when you encounter corruption. By the end of this session you will be ready to take on corruption, one database at a time.

Read More »When Corruption Strikes – Online Presentation Wed May 13th

Corruption Challenge 4 – Second Alternate Solution.

The following is a solution to Corruption Challenge #4 that uses DBCC WritePage to fix the corruption.  Before proceeding, be sure to read this warning.

WARNING: DBCC WritePage is a dangerous command, that should never be used on any production database. It may invalidate your ability to get support from Microsoft on issues that arise with that database going forward. It is not my intention to encourage anyone to use DBCC WritePage ever. This is just what I used to create a corrupt database, and since creating corrupt databases is not part of the role of most DBAs, you should not use DBCC WritePage. Consider yourself warned.

DBCC WritePage is a cool, but extremely risky way to solve this weeks Database Corruption Challenge, however, do NOT EVER RUN THIS ON A PRODUCTION SYSTEM.

Read More »Corruption Challenge 4 – Second Alternate Solution.

Database Health Monitor -Beta Version 10.1 Released Today

Today is the release of Database Health Monitor Beta 10.1. If you haven’t see the Database Health Monitor before, its a free application that I have been building over last 4 years.

It has been a while since the last release, so I thought I would just walk through and show a few of the features.

One thing that changed right off was in the previous version, these was a advertisement for my IndieGoGo campaign where I attempted to crowd fund the next phase of development on Database Health Reports. The IndieGoGo campaign has been removed.

Real Time Reports

The real time reports are a category of reports that query the database directly to find out something that is currently happening right now. See the Historic Reports below for the ability to find out what was happening at some point in the past.

One of the new reports added is the Suspect Paged report. If you have been participating the Database Corruption Challenge you will certainly understand the value of this report.

SuspectPages

Read More »Database Health Monitor -Beta Version 10.1 Released Today

Corruption Challenge 4 – Alternate Solution

Here is the solution provided by Patrick Flynn. Congratulations Patrick for being one of this weeks winners.

I really liked this solution for a number  of reasons, first it showed how to pull data from DBCC Page and actually convert it into rows and columns that could then be inserted back into a table. Secondly due to its use of Common Table Expressions.

 

This was done using SQL Server 2014.

Approach used was:

(a) Restore and check database – Corruption is related to Allocation Meta Data for dbo.Customers

(b) Use the non-clustered indexes on FirstName and LastName to recover data apart from Middle name – Total of 511740 rows in Table
Steps are in CorruptionChallange Setup.sql (requires disabling Database triggers

(c) Cannot directly select data from pages due to allocation issues but can select raw data vis DBCC Page (and DBCC IND)
Requires using DBCC Ind to get list of Data pages and DBCC Page to extract data
Steps to extract and Parse data are in DBCCPage_Extract

Read More »Corruption Challenge 4 – Alternate Solution