My nightly DBCC CheckDB job is failing on the CRM Server, when it checks the MSCRM database. It fails because the SPID is blocking itself: and there's no point in trying to kill the SPID, because that just hangs. The self-blocking SPID also blocks everything else, rendering it almost impossible to stop the Scheduled Job via SSMS, as SQL Agent doesn't load. The self-blocking SPID also renders it impossible to stop SQL via SSMS or SQL Configuration Manager: the only way to stop SQL seems to be by killing the process in Task Manager (and that can't possibly be healthy).
There is, if one checks the sys.dm_os_waiting_tasks DMV, a lot of action going on in tempdb while this process is failing: the blocked resource is blocked on a LCK_M_IX wait_type, which is an intent exclusive lock. The memory involved in the process seems to be rather intensive (the memory used by SQL suddenly skyrockets). The executing SQL that is involved is really quite interesting: and not many people seem to have come across it based on a quick google about the place.
DECLARE @BlobEater VARBINARY(8000)
SELECT @BlobEater = CheckIndex (ROWSET_COLUMN_FACT_BLOB)
FROM { IRowset 0xE093909D00000000 }
GROUP BY ROWSET_COLUMN_FACT_KEY
>> WITH ORDER BY
ROWSET_COLUMN_FACT_KEY,
ROWSET_COLUMN_SLOT_ID,
ROWSET_COLUMN_COMBINED_ID,
ROWSET_COLUMN_FACT_BLOB
OPTION (ORDER GROUP)
So far, I have drawn a complete blank. However, I've decided that trying to increase the size of TempDB might be a sensible thing to do, simply because so much that is being blocked involves tempdb, and tempdb itself was about 8MB in size.
Showing posts with label DBCC CHECKDB. Show all posts
Showing posts with label DBCC CHECKDB. Show all posts
Monday, 13 September 2010
Tuesday, 5 January 2010
Beware Third Party Software: it can do odd things
DBCC CHECKDB on (naturally) the largest database we have, the one which supports the mainwebsite and all the forums, failed.... PANIC! PANIC!
DBCC results for 'MyDB'.Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:576726) with latch type SH. 38
(failed to retrieve text for this error. Reason: 15105) failed.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object
.CHECKDB found 0 allocation errors and 1 consistency errors in database 'MyDB'.
is the minimum repair level for the errors found by DBCC CHECKDB (MyDB).
Msg 5269, Level 16, State 1, Line 1
Check terminated. The transient database snapshot for database 'MyDB' (database ID 6) has been marked suspect due to an IO operation failure. Refer to the SQL Server error log for details.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
The SQL Error Logs showed a sad tale:
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a4000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a8000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a6000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199aa000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a0000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a2000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199ae000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
The operating system returned error 1784(failed to retrieve text for this error. Reason: 15105) to SQL Server during a write at offset 0x00000074b48000 in file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24'. 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.
Error: 17053, Severity: 16, State: 1.O:\DATA\MyDB.mdf:MSSQL_DBCC24: Operating system error 1784(failed to retrieve text for this error. Reason: 15105) encountered.
Enough to give anyone the screaming habdabs, right? SQL 2008, on the SAN, a really important database, and it can't complete a DBCC CHECKDB owing to I/O issues.
Oddly, every other DBCC CHECKDB (bar one, which showed page errors) completed successfully. The SAN guy couldn't find any problems with the SAN I/O, and there hadn't been any changes to the switches.
What else? Well, we use Diskeeper 2010 to keep the database drives defragged (it works rather well), and it would seem that the new Intelliwriter Option causes DBCC CHECKDB to react badly, as the file expands to allow for non-fragmented writing, and DBCC CHECKDB doesn't expect this. I am not the only DBA who has had the problem. Diskeeper have produced a fix, which is available here, but in the short term, disabling Intelliwriter corrects the issue - and DBCC CHECKDB will complete happily and there is no need to panic.
Still, my next move will be to write an SSIS package to connect to all the servers and run DBCC CHECKDB on each database in turn....
DBCC results for 'MyDB'.Msg 8966, Level 16, State 2, Line 1
Unable to read and latch page (1:576726) with latch type SH. 38
(failed to retrieve text for this error. Reason: 15105) failed.
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object
.CHECKDB found 0 allocation errors and 1 consistency errors in database 'MyDB'.
is the minimum repair level for the errors found by DBCC CHECKDB (MyDB).
Msg 5269, Level 16, State 1, Line 1
Check terminated. The transient database snapshot for database 'MyDB' (database ID 6) has been marked suspect due to an IO operation failure. Refer to the SQL Server error log for details.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
The SQL Error Logs showed a sad tale:
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a4000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a8000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a6000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199aa000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a0000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199a2000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
A read of the file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24' at offset 0x000001199ae000 succeeded after failing 1 time(s) with error: 38(failed to retrieve text for this error. Reason: 15105). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
The operating system returned error 1784(failed to retrieve text for this error. Reason: 15105) to SQL Server during a write at offset 0x00000074b48000 in file 'O:\DATA\MyDB.mdf:MSSQL_DBCC24'. 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.
Error: 17053, Severity: 16, State: 1.O:\DATA\MyDB.mdf:MSSQL_DBCC24: Operating system error 1784(failed to retrieve text for this error. Reason: 15105) encountered.
Enough to give anyone the screaming habdabs, right? SQL 2008, on the SAN, a really important database, and it can't complete a DBCC CHECKDB owing to I/O issues.
Oddly, every other DBCC CHECKDB (bar one, which showed page errors) completed successfully. The SAN guy couldn't find any problems with the SAN I/O, and there hadn't been any changes to the switches.
What else? Well, we use Diskeeper 2010 to keep the database drives defragged (it works rather well), and it would seem that the new Intelliwriter Option causes DBCC CHECKDB to react badly, as the file expands to allow for non-fragmented writing, and DBCC CHECKDB doesn't expect this. I am not the only DBA who has had the problem. Diskeeper have produced a fix, which is available here, but in the short term, disabling Intelliwriter corrects the issue - and DBCC CHECKDB will complete happily and there is no need to panic.
Still, my next move will be to write an SSIS package to connect to all the servers and run DBCC CHECKDB on each database in turn....
Subscribe to:
Posts (Atom)