Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

db page corruption Expand / Collapse
Author
Message
Posted Thursday, August 19, 2010 10:56 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2014 12:45 AM
Points: 153, Visits: 601
hi,

I am a SQL server DBA concentrating more in Internals and performance tuning area.
like to know in which scenario a page will be corrupted? how sql server understands that the page is corrupted?
i know almost 70% of data corruption cases can be rectified without or with minimum data loss.

and also like to know whether we could avoid this kind of page corruption with some best practices?

"The In-row data RSVD page count for object "SMS_AssociateLocation", index ID 0, partition ID 71668789018624, alloc unit ID 71668789018624 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. CHECKDB found 0 allocation errors and 1 consistency errors in table 'SMS_AssociateLocation' (object ID 1093578934). CHECKDB found 0 allocation errors and 1 consistency errors in database 'CRS_ISG'.". Possible failure reasons: Problems with the query"

thanks in advance
ram
Post #972281
Posted Friday, August 20, 2010 1:48 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, December 20, 2013 8:21 AM
Points: 61, Visits: 699
Hi,

If you have read through this section, you know that page corruption happens in most cases because of hardware failures, or filter driver misbeheaviour. SQL Server asks for a page at a particular offset of the file, and then many issues can happen. The operating system may not be able to read the data, or the page returned does not contain the data that was requested, or its header is unreadable, or its contents have been modified...

Your best friend will always be a reliable backup strategy. Be sure you know how to restore or to failover quickly, but most of all keep in mind that you'll always need to do root cause analysis on your damaged system. If you don't, nothing prevents this from happening again.

You can't avoid hardware failures, because they are mostly unpredictable. Just be sure you have rock-solid backup strategy and a failover system (I mean both of them), backup your databases with checksums enabled and verify the checksums, eventually restore your backup on a day-1 database, enable page checksums at the database level, don't put your transaction log, your data files and your backups at the same place, run dbcc checkdb at regular intervals and monitor the errors. A lot of people in this section are hit by corruption, and because they never run checkdb or never monitor the results, they realize they don't know when their last known good backup was, and they end up with repair_allow_data_loss.

If you are running Enterprise edition, you can count on very cool features, like page restore or mirrored backups.

The error you mention is not exactly a corruption, and can be fixed by running DBCC UPDATEUSAGE. I know they can happen after an upgrade from SQL Server 2000. You should always run DBCC UPDATEUSAGE after an upgrade.

Hope this helps,

David B.


David B.
Post #972332
Posted Friday, August 20, 2010 1:57 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 42,485, Visits: 35,554
geramkumar (8/19/2010)
I am a SQL server DBA concentrating more in Internals and performance tuning area.
like to know in which scenario a page will be corrupted? how sql server understands that the page is corrupted?


Corruption means that a page's structure is no the way it should be. Either there's incorrect values for parts of the page, the page checksum is wrong, the page is completely unreadable (The OS returns an error when asked for it), or one or more rows on the page have incorrect values in places

i know almost 70% of data corruption cases can be rectified without or with minimum data loss.

I wouldn't put a figure on it. I do know that, with an appropriate backup strategy and regular integrity checks, almost all corruption cases can be recovered without data loss. That depends on knowing about the corruption early enough that restoring from backup is an option (and on having a backup to restore from)

Take a look at this article. http://www.sqlservercentral.com/articles/65804/



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #972339
Posted Sunday, August 22, 2010 11:30 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, July 24, 2014 12:45 AM
Points: 153, Visits: 601
Thanks David BAFFALEUF and Gila Monster for your reply.
Post #973199
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse