I am often asked by professionals: Is checkdb still recommended in SQL Server 2008? Calculate the checksum from SQL 2005 to prevent physical page corruption. However, in my experience (since SQL Server 7.0), I have never clearly indicated that checkdb is not necessary.
Do other people have good suggestions?
_____________________________________________
From:Robert Dorr
Sent:Friday, February 20,200 9 AM
This issue has been mentioned many times. Indeed, the addedDatabase page audit checksum(Database page audit checksum) reduces the number of checkdb executions. Historical records also show that the performance of the database engine has been significantly improved since SQL Server 7.0, further reducing this necessity.
However, features such as validation are only guaranteedWhat is written to the disk and what is read. It does not validate many other data integrity issues, and checkdb is designed for this. For example, checkdb verifies the metadata, page chain, and index sorting of system tables.
The answer to this question is: from SQL Server 7.0, this necessity has been reduced. A strict recovery plan should include full database backup and checkdb to ensure that all aspects of the plan are available. For example, you have a clean database, which is backed up and used for restoration. It is damaged because something happened during the backup media migration process. Backup with checksum in sql2008 can be used in this scenario. Some vendors said: This is never necessary, and the database engine can handle everything. I think this is a bit one-sided.
For example:
- I have handled a case of inconsistent tape drives. It can be successfully backed up to the tape but cannot be read for restoration. Unless the restoration operation is performed, the database engine will not be aware of hardware errors. This customer lost his production server and could not recover it. Therefore, $100,000 was spent on a tape recovery company.
-
- I have studied memory and ECC failure rates. According to research, a single bit change in memory transmission can cause data corruption. This is why SQL Server 2005 adds a constant page scan (constant page scanning) to the buffer pool ). When pages are in memory, constant page scan technology samples checksum to ensure that read-only pages do not change due to such errors. This technology uses compaction to ensure data stability, but cannot capture everything. Therefore, an ECC fault may still occur, and pages in the cache pool may be changed and the checksum and write operations are successfully calculated and written to the disk. I once handled this problem in one case: a single bit in the middle of a data value is changed. This column happens to be a primary key referenced by a foreign key. Therefore, if the primary key value is 9, it may be changed to 19. Because of the repeated value 19, the primary key constraint is violated, and the foreign key reference value 9 does not exist. This data change is not affected by the transaction, but caused by any value change on the page. This database is configured with log transfer, and the slave machine does not display this error. The result is "more mixed water ". Because it is not SQL server that directly changes the primary key value, the database log will not record this error. Therefore, checkdb cannot detect any problems even if a clean restoration is performed. Running DBCC checkdb on the master database is the only way to discover this problem before performing full or differential backup and making it a historical archive backup.
-
- Like ecc SQL Server bugs, more xproc and COM Object bugs cause the same error. IfCodeIf the memory is incorrectly updated, an error in the same mode will occur.
- Stale read (returns the previous version of the page from the hardware cache instead of the latest version) is the top problem in terms of storage in the past 10 years ). because this is only an older version of the page, the checksum test usually does not fail. In this scenario, my wife saves $500 to our current account. In a few minutes, I will retrieve $500. The hardware cache will load the balance of my wife's account before the deposit. My wife's deposit was submitted and written to the disk, but the hardware cache was not updated. When I fail to find the deposit when I get the money, I mistakenly use the initial account balance to update my account. SQL Server does not directly know these things, so this becomes a loss of logical transactions.
In this case, this error cannot be found even if DBCC is executed. In addition to recovering the database from the transaction log backup, and finding out which page is updated twice by the transaction with the same lsn, this is an invalid condition.
I never want to say that DBCC is no longer needed. You should run it from time to time like restoring a backup.
Joe. TJ is used only for the purpose of disseminating information.
Original article address:SQL Server: Is checkdb a necessity?
