Databse Suspect Error - Please Help!
-
We have SQL 2000 (SP3 or 4). I had to killed an Update Process that was blocking a DBCC Process. I then rebooted the Server and the database came back in "suspect" mode. The SQL Error Logs returned the following:
I/O error (torn page) detected during read at offset 0x000008f85c4000 in file 'E:\Program Files\Microsoft SQL Server\MSSQL\Data\PEMASTER.mdf'..
Error while redoing logged operation in database 'PEMASTER'. Error at log record ID (110369:131409:531)..
Where do I start in correcting this issue?
Thanks in advance for any help you can provide, Kevin
-
I would start by running resetting the suspect flag using: sp_resetstatus [ @DBName = ] 'database'. Then run DBCC CHECKDB on all of your databases to see how big the damage is. Is this a production DB? If this does not work, you may have to restore from backup. Good luck....
-
I hope you have a good backup/recovery strategy as this will be your most likey approach. Here is a quote from MSDN:
TORN_PAGE_DETECTION
This recovery option allows SQL Server to detect incomplete I/O operations caused by power failures or other system outages.
When set to ON, this option causes a bit to be reversed for each 512-byte sector in an 8-kilobyte (KB) database page when the page is written to disk. If a bit is in the wrong state when the page is later read by SQL Server, the page was written incorrectly; a torn page is detected. Torn pages are usually detected during recovery because any page that was written incorrectly is likely to be read by recovery.
Although SQL Server database pages are 8 KB, disks perform I/O operations using a 512-byte sector. Therefore, 16 sectors are written per database page. A torn page can occur if the system fails (for example, due to power failure) between the time the operating system writes the first 512-byte sector to disk and the completion of the 8-KB I/O operation. If the first sector of a database page is successfully written before the failure, the database page on disk will appear as updated, although it may not have succeeded.
Note Using battery-backed disk caches can ensure that data is successfully written to disk or not written at all.
If a torn page is detected, an I/O error is raised and the connection is killed. If the torn page is detected during recovery, the database is also marked suspect. The database backup should be restored, and any transaction log backups applied, because it is physically inconsistent.
By default, TORN_PAGE_DETECTION is ON.
The current setting of this option can be determined by examining the IsTornPageDetectionEnabled property of DATABASEPROPERTYEX.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply