August 19, 2010 at 10:56 pm
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
August 20, 2010 at 1:48 am
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.
August 20, 2010 at 1:57 am
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
August 22, 2010 at 11:30 pm
Thanks David BAFFALEUF and Gila Monster for your reply.
August 25, 2017 at 5:14 am
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply