SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Identifying what caused Corruption


Identifying what caused Corruption

Author
Message
winston Smith
winston Smith
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1767 Visits: 2064
I had a database in suspect mode this morning.
Fixed it by:
-placing in emergency mode.
-set single_user mode
-dbcc checkdb(N'dbname', REPAIR_ALLOW_DAT_LOSS)
-Rebuild the log.
-bring online.

DBCC Checkdb returned a bunch of errors:


Extent (3:394032) in database ID 7 is allocated by more than one allocation object.
The error has been repaired.

Msg 8913, Level 16, State 1, Line 2
Extent (3:394032) is allocated to 'GAM' and at least one other object.
The error has been repaired.



--Lots of these

Repair: Deleted off-row data column with ID 562825134080, for object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044153856 (type LOB data) on page (3:393971), slot 28.
Repair: Deleted off-row data column with ID 562825199616, for object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044153856 (type LOB data) on page (3:393971), slot 29.
Repair: Deleted off-row data column with ID 562825265152, for object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044153856 (type LOB data) on page (3:393971), slot 30.




Lots of these:

Msg 8945, Level 16, State 1, Line 2
Table error: Object ID 1076927654, index ID 1 will be rebuilt.
The error has been repaired.
Msg 8977, Level 16, State 1, Line 2
Table error: Object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044088320 (type In-row data). Parent node for page (3:394009) was not encountered.
The error has been repaired.
Msg 8977, Level 16, State 1, Line 2
Table error: Object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044088320 (type In-row data). Parent node for page (3:394024) was not encountered.
The error has been repaired.
Msg 8914, Level 16, State 1, Line 2
Incorrect PFS free space information for page (3:393971) in object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044153856 (type LOB data). Expected value 100_PCT_FULL, actual value 50_PCT_FULL.
The error has been repaired.
Msg 8964, Level 16, State 1, Line 2
Table error: Object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044153856 (type LOB data). The off-row data node at page (3:393971), slot 28, text ID 562825134080 is not referenced.
The error has been repaired.
Msg 8964, Level 16, State 1, Line 2
Table error: Object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044153856 (type LOB data). The off-row data node at page (3:393971), slot 29, text ID 562825199616 is not referenced.
The error has been repaired.
Msg 8964, Level 16, State 1, Line 2
Table error: Object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044153856 (type LOB data). The off-row data node at page (3:393971), slot 30, text ID 562825265152 is not referenced.



Im googling these errors, but im struggling to find out what could have caused this. Any help would be appreciated.

Thanks a mil!
free_mascot
free_mascot
Hall of Fame
Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)Hall of Fame (4K reputation)

Group: General Forum Members
Points: 3959 Visits: 2235
Pl. find any other error in windows error log.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
winston Smith
winston Smith
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1767 Visits: 2064
free_mascot (5/7/2014)
Pl. find any other error in windows error log.


There was windows security patching performed, and an unexpected shutdown.

Unexpected shutdown is worrying, but i assumed SQL can handle these by rolling back anything that was in flight when the cut/shutdown happened.
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19764 Visits: 17242
Do you understand the ramifications of the below??

winston Smith (5/7/2014)
Repair: Deleted off-row data column with ID 562825134080, for object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044153856 (type LOB data) on page (3:393971), slot 28.
Repair: Deleted off-row data column with ID 562825199616, for object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044153856 (type LOB data) on page (3:393971), slot 29.
Repair: Deleted off-row data column with ID 562825265152, for object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044153856 (type LOB data) on page (3:393971), slot 30.


Congratulations you've just dropped Lords knows how much data from your database, was that your desired intention? Didn't you have a backup you could have restored?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
winston Smith
winston Smith
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1767 Visits: 2064
Perry Whittle (5/7/2014)
Do you understand the ramifications of the below??

winston Smith (5/7/2014)
Repair: Deleted off-row data column with ID 562825134080, for object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044153856 (type LOB data) on page (3:393971), slot 28.
Repair: Deleted off-row data column with ID 562825199616, for object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044153856 (type LOB data) on page (3:393971), slot 29.
Repair: Deleted off-row data column with ID 562825265152, for object ID 1076927654, index ID 1, partition ID 72057594039566336, alloc unit ID 72057594044153856 (type LOB data) on page (3:393971), slot 30.


Congratulations you've just dropped Lords knows how much data from your database, was that your desired intention? Didn't you have a backup you could have restored?




I 100% completely understand what i did, i wouldn't have done it otherwise. And yes, I have a backup. The db only has 4 tables and is only for logging of agent data (not sql agent). I needed to get it online asap to prevent issues with agents/services/whatever you want to call them.
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39283 Visits: 32618
All the errors you're listing are just the symptoms from the allocation errors. They're not going to help you troubleshooting the origin of the problem. Instead, you need to look back in the SQL Server error log and the windows error log to see if you can find indications of what happened prior to you deleting some portion of the database. That will help you figure out what happened.

If you have a backup, do you know it's clean? Could you restore it to a second location and run DBCC on it (without using REPAIR) to see if you can identify allocation errors?

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86518 Visits: 45242
Corruption is 99.99% IO subsystem problem. Not necessarily the disks, could be anything anywhere in the IO stack.
Since you mentioned an uncontrolled shutdown, I'd check and make sure all write caches are battery-backed (with working batteries) or disabled.

Without seeing the full results of a CheckDB (not one run with repair), it's very, very hard to say more. There's not enough information.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12286 Visits: 8542
GilaMonster (5/7/2014)
Corruption is 99.99% IO subsystem problem. Not necessarily the disks, could be anything anywhere in the IO stack.
Since you mentioned an uncontrolled shutdown, I'd check and make sure all write caches are battery-backed (with working batteries) or disabled.

Without seeing the full results of a CheckDB (not one run with repair), it's very, very hard to say more. There's not enough information.


I agree. But given the extent of the problem (pun intended :-P ) I doubt it is from the shutdown itself, or even multiple unexpected shutdowns. This seems systemic, and that goes to entire-IO-stack firmware and drivers (and maybe other stuff you may have on your system like antivirus, disk defraggers, file-filter-driver thingies. It could also be a bad chip somewhere leading to corruptions too, but that is WAY less likely than firmware/drivers.

It is a virtual certainty that you will continue to get these errors until you patch everything. And honestly I would IMMEDIATELY take this system offline and never use it for any work I cared about again until I had beat the sh!t out of it to validate that my patching everything in sight prevented any errors from occurring. And maybe not even then.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86518 Visits: 45242
TheSQLGuru (5/7/2014)
But given the extent of the problem (pun intended :-P ) I doubt it is from the shutdown itself, or even multiple unexpected shutdowns.


I'm not willing to guess without a lot more information. There's no where near enough detail for me to make an accurate diagnostic or prediction.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


TheSQLGuru
TheSQLGuru
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12286 Visits: 8542
GilaMonster (5/7/2014)
TheSQLGuru (5/7/2014)
But given the extent of the problem (pun intended :-P ) I doubt it is from the shutdown itself, or even multiple unexpected shutdowns.


I'm not willing to guess without a lot more information. There's no where near enough detail for me to make an accurate diagnostic or prediction.


Fair enough, although I do not consider my doubt/assertion a true guess and certainly not a random guess. It is a probabilistic analysis of information (i.e. LOTS of corruptions mentioned multiple times) that I believe is sufficient to rule out a particular cause the (vast?) majority of the time.

Also note that you stated that 9,999 times out of 10,000 that the IO subsystem was responsible for corruptions. Same type of probabilistic statement - just with a higher level of certainty. Cool

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search