Msg 2534 : DBCC CHECKDB allocation errors

Question

We recently moved our intranet production database from an SQL Server 2008 to an SQL Server 2008 R2 brand new server.

The backup Job have been failing since migrating, due to DBCC Errors (Check database integrity task).

I tried to run a DBCC CHECKDB using the repair_rebuild option on the production database yersterday, and the a subsequent DBCC CHECKDB showed no errors. The DBCC CHECKDB against the production databas is showing again error messages, the backup is done, but of course the maintenance plan fails: I have (a long list of) the following error messages:

Table error: page (1:1100029), whose header indicates that it is allocated to object ID 373210913, index ID 1, partition ID 72057701886525440, alloc unit ID 72057702118457344 (type In-row data), is allocated by another object.
Msg 2534, Level 16, State 2, Line 1
Table error: page (1:1100030), whose header indicates that it is allocated to object ID 373210913, index ID 1, partition ID 72057701886525440, alloc unit ID 72057702118457344 (type In-row data), is allocated by another object.
Msg 7965, Level 16, State 1, Line 1
Table error: Could not check object ID 373210913, index ID 1, partition ID 72057701886525440, alloc unit ID 72057702118457344 (type In-row data) due to invalid allocation (IAM) page(s).
Msg 2534, Level 16, State 2, Line 1
Table error: page (1:1100031), whose header indicates that it is allocated to object ID 373210913, index ID 1, partition ID 72057701886525440, alloc unit ID 72057702118457344 (type In-row data), is allocated by another object.
Msg 2534, Level 16, State 2, Line 1
Table error: page (1:1100200), whose header indicates that it is allocated to object ID 373210913, index ID 1, partition ID 72057701886525440, alloc unit ID 72057702118457344 (type In-row data), is allocated by another object.
Msg 2534, Level 16, State 2, Line 1
Table error: page (1:1100201), whose header indicates that it is allocated to object ID 373210913, index ID 1, partition ID 72057701886525440, alloc unit ID 72057702118457344 (type In-row data), is allocated by another object.
There are 14 rows in 3 pages for object "searchfull_dummy_1049".
CHECKDB found 0 allocation errors and 6 consistency errors in table 'searchfull_dummy_1049' (object ID 373210913).
DBCC results for 'foldersRecherche'.
There are 0 rows in 0 pages for object "foldersRecherche".
DBCC results for 'wf'.
There are 0 rows in 0 pages for object "wf".
DBCC results for 'searchfull_dummy_1663'.

I tried to use yesterday's backup , restored the database on the same server with a new db name, and ran a DBCC Update usage(0) on the database, then DBCC CHECKDB showed no more errors on the newly restored database.

I applied the same procedure to the production database but this did not fix the issue on the production database. I'm getting the same errors as above.

Answer 1

If the sequence is:

1. DBCC CHECKDB reported errors.
2. REPAIR_REBUILD fixed the database, no errors reported.
3. DBCC CHECKDB reporting errors again.

You probably have failed or failing disks in an array or some other component of the IO subsystem is broken, or breaking. Personally, I'd want off the problem server ASAP!

• Take a tail-log backup.
• Restore last known good full backup and diff/log sequence to a different server.
• Switch service to the second server.
• Get a fresh cuppa and start diagnosis on the problem server.

Addendum: Root cause of the problem:

as it turned out, I found that as we are using Diskeeper, we are (for corporate reasons) still using an old version : 14.0.896. With this version we run into messages like :

The operating system returned error 1784(failed to retrieve text for this error. Reason: 15100) to SQL Server during a write at offset 0x000000010a4000 in file 'D:\Application-Data\MSSQL\Data\<Database>.mdf:MSSQL_DBCC17'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
Msg 3313, Level 21, State 2, Server <Server>, Line 1
During redoing of a logged operation in database '<Database>', an error occurred at log record ID (2349664:1503:4). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database.
Msg 1823, Level 16, State 1, Server <Server>, Line 1
A database snapshot cannot be created because it failed to start.
Msg 1823, Level 16, State 2, Server <Server>, Line 1
A database snapshot cannot be created because it failed to start.
Msg 7928, Level 16, State 1, Server <Server>, Line 1
The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline.
Msg 5030, Level 16, State 12, Server <Server>, Line 1
The database could not be exclusively locked to perform the operation.
Msg 7926, Level 16, State 1, Server <Server>, Line 1
Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details

Found an explanation on Paul Randal's blog here

Link to DiskKeeper Fix found here

Interesting Diskeeper white paper on database defragmentation here