Database Corruption Video – HA DR Chapter
The following Database Corruption Video is from the High Availability Disaster Recovery Virtual Chapter Presentation on September 13, 2016.
Today I had the opportunity to present at the High Availability Disaster Recovery Virtual Chapter meeting on the topic of Database Corruption. Here is a recording of the presentation.
Database Corruption Video
If you liked this presentation, please come to my session on Database Corruption at PASS Summit 2016 in Seattle.
You can download todays presentation and the sample code here: hadr_chapter_presentation_2016.zip This includes the corrupt databases files as well as the sql scripts to fix them and the presentation material from todays session.
Database Corruption Challenges
- Week 1 Database Corruption Challenge 4/11/2015 won by Brent Ozar. (See the solution)
- Week 2 Database Corruption Challenge 4/17/2015 won by Rob Farley. (See the solution)
- Week 3 Database Corruption Challenge 4/24/2015 won by Randolph West. (See the solution)
- Week 4 Database Corruption Challenge 5/1/2015 won by Randolph West. (See the solution)
- Week 5 Database Corruption Challenge 5/12/2015 won by Patrick Flynn. (See the solution)
- Week 6 Database Corruption Challenge 5/23/2015 won by Raul Gonzalez. (See the solution)
- Week 7 Database Corruption Challenge 6/2/2015 – won by Raul Gonzalez. (See the solution)
- Week 8 Database Corruption Challenge 6/19/2015 – won by Rob Farley. (See the solution)
- Week 9 Database Corruption Challenge 7/6/2015 – won by Eduardo Rezende. (See the solution)
- Week 10 Database Corruption Challenge 7/17/2015 – won by Rob Farley.
Related Links
Need help with Database Corruption… That is one of my specialties. Contact me I can help resolve your corruption issues.
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Steve,
Thank you so much for sharing your knowledge with the SQL Server community. This video was awesome and I learned a lot. Keep up the great work.
Hi Steve,
Thanks for the great presentation you have delivered.
my question is: as a DBA i shouldn’t look at the data inside the tables…can i use select count(*) instead of select * from the table whenever i face a corruption situation OR this won’t provide me with accurate information of how much data i can pull ??!!
The behavior is slightly different between the two
SELECT * to examine a table will perform differently than SELECT COUNT(*) as SQL Server uses a shortcut on SELECT COUNT(*) and scans the narrowest nonclustered index to get the count rather than looking at the clustered index. If there are no nonclustered indexes then SELECT * would scan the clustered index.
Hope this helps.
-Steve Stedman
Steve,
Do you have any blog post on resolving the issue below? (IAM) pointer issue.
Msg 2576, Level 16, State 1, Line 1
The Index Allocation Map (IAM) page (0:0) is pointed to by the previous pointer of IAM page (1:32707) in object ID 0, index ID -1, partition ID 0,
alloc unit ID 323331337027584 (type Unknown), but it was not detected in the scan.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=638677373) of row (object_id=638677373,column_id=1)
in sys.columns does not have a matching row (object_id=638677373) in sys.objects.
Msg 8992, Level 16, State 1, Line 1
Check Catalog Msg 3853, State 1: Attribute (object_id=638677373) of row (object_id=638677373,column_id=2)
in sys.columns does not have a matching row (object_id=638677373) in sys.objects.
James,
I don’t have a specific example showing how to fix that problem. It looks like your system tables are corrupt, perhaps relating to sys.objects, or sys.columns.
Given an index ID of -1 in the error message and the IAM Page of 0:0 with an object ID of 0 that makes me think that some page header has been stomped on. Do you have a backup prior to the corruption that you could compare sys.columns, or sys.objects.
I would first start by backing this up and restoring it somewhere that you can safely work on it.
If you can still get to all the data in your tables, you may need to create a new database with the matching schema and copy everything over.
If you need more help, let me now.
-Steve Stedman