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


db page corruption


db page corruption

Author
Message
Ramkumar (LivingForSQLServer)
Ramkumar (LivingForSQLServer)
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 620
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
David BAFFALEUF
David BAFFALEUF
SSC Veteran
SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)SSC Veteran (247 reputation)

Group: General Forum Members
Points: 247 Visits: 712
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.
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: 86879 Visits: 45263
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, 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


Ramkumar (LivingForSQLServer)
Ramkumar (LivingForSQLServer)
SSC Veteran
SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)SSC Veteran (255 reputation)

Group: General Forum Members
Points: 255 Visits: 620
Thanks David BAFFALEUF and Gila Monster for your reply.
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