Skip to content

Posts tagged ‘consistency errors’

What caused this database corruption?

Most database corruption (database consistency errors) comes from hardware issues mainly disk issues. The first place to look is, IO subsystem not behaving well. If there are issues then there it could just be a matter of time before other databases on same drive gets corrupted.

Try looking into the system event log. You may find disk related errors which could be the root cause of your corruption.

You may see disk related errors like:

01/12/2010 19:54:36 Cissesrv Error None 24606 N/A TESTSERVER01 "Logical drive 3 configured on array controller P410i [Embedded] returned a fatal error during a read/write request from/to the volume.
Logical block address 257513711, block count 1024 and command 32 were taken from the failed logical I/O request.
Array controller P410i [Embedded] is also reporting that the last physical drive to report a fatal error condition (associated with this logical request), is located on bus 0 and ID 3. "
01/12/2010 19:54:36 Cissesrv Error None 24606 N/A TESTSERVER01 "Logical drive 3 configured on array controller P410i [Embedded] returned a fatal error during a read/write request from/to the volume.
Logical block address 257513711, block count 1024 and command 32 were taken from the failed logical I/O request.
Array controller P410i [Embedded] is also reporting that the last physical drive to report a fatal error condition (associated with this logical request), is located on bus 0 and ID 3. "
01/12/2010 19:54:18 dmio Warning None 35 N/A TESTSERVER01 dmio: Disk Harddisk2 block 257512687 (mountpoint E:): Uncorrectable read error
01/12/2010 19:54:18 dmio Information None 29 N/A TESTSERVER01 dmio: Harddisk2 read error at block 257512687: status 0xc0000185
01/12/2010 19:54:18 Disk Error None 11 N/A TESTSERVER01 The driver detected a controller error on \Device\Harddisk2. 

If you find any disk related issue, you need to engage your platform team to fix the issue or replace the disk as per error messages. Like, the above error messages leads to a disk failure if no action is taken.

Master database backup failed – Error: “ResultSet” property not set correctly

Error

Backup of master database is failing with error message: –

Executing the query "BACKUP DATABASE [master] TO DISK = N'e:\\MSSQL10.M..." failed with the following error: "Read on "e:\\MSSQL10.MSSQLSERVER\\MSSQL\\DATA\\master.mdf" failed: 1117(failed to retrieve text for this error. Reason: 1815)
BACKUP DATABASE is terminating abnormally.
10 percent processed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Try to run DBCC checkdb on master DB. You will find consistency errors like: –

DBCC results for 'sys.sysprufiles'.
Msg 2533, Level 16, State 1, Line 1
Table error: page (1:73) allocated to object ID 24, index ID 1, partition ID 281474978283520, alloc unit ID 281474978283520 (type In-row data) was not seen. The page may be invalid or may have an incorrect alloc unit ID in its header.
Msg 8980, Level 16, State 1, Line 1
Table error: Object ID 24, index ID 1, partition ID 281474978283520, alloc unit ID 281474978283520 (type In-row data). Index node page (0:0), slot 0 refers to child page (1:73) and previous child (0:0), but they were not encountered.
There are 0 rows in 0 pages for object "sys.sysprufiles".
CHECKDB found 0 allocation errors and 2 consistency errors in table 'sys.sysprufiles' (object ID 24).

Resolution

Master is very rarely changed except for logins, new databases, startup stored procedures, system configuration settings, ect.

If you have a recent backup of master database then prefer restoring master databse from good backup, assuming there haven’t been any or many┬áchanges there, it will be easier to restore master.

If you see the DBCC output in details and see tha the corruption is in index id of 1 which is a clustered index which means the chances of being able to repair it with a repair_rebuild will be very small. But you can try doing this as a second option (if you don’t have backup a recent backup or many cahges were made after that backup).