An Internal Error Occurred In Dbcc Which Prevented Further Processing
Contents |
that people are hitting - Msg 8967, Level 16, State 216By: Paul Randal Posted on: May 22, 2008 10:20 pm Before I start, I want to make it clear that you dbcc checkdb tablock can only hit this bug if you ALREADY have corruption, that it's quite rare, dbcc checkdb with tablock syntax and that there is a workaround. I've noticed a few more people in the forums having CHECKDB fail with this particular dbcc checkdb tablock example error in the last month Msg 8967, Level 16, State 216, Line 1 An internal error occured in DBCC which prevented further processing. Please contact Product Support. instead of completing properly and listing the corruptions in msg 8921 sql server the database. Whenever CHECKDB is using a database snapshot, it must check that the page it read through the snapshot does not have an LSN (Log Sequence Number) higher than that when the snapshot was created. If it did, this would mean that the page was modified AFTER the snapshot was created and hence CHECKDB would be working from an inconsistent view of the database. If this case is discovered, CHECKDB stops
System Table Is Inconsistent
immediately. When I rewrote CHECKDB for SQL Server 2005, I changed a bunch of code assertions into seperate states of the 8967 error, so that CHECKDB would fail gracefully if some condition occured that indicates a bug or something that should never happen. State 216 is for the bad LSN condition I've just described. I used to think it was caused by a race condition with the NTFS code that implements sparse files, which is used by the hidden database snapshot that CHECKDB uses by default. However, I've come to learn that this is a bug in CHECKDB (not one of mine I should say :-)) that causes this behavior under certain circumstances when corruption is present. The bug is that if a corrupt page fails auditing inside CHECKDB, the LSN check is still performed. If the corruption affects the LSN stamped in the page header, the 8967 error could be triggered. I've seen this a handful of times in the last few weeks - hence the need for a blog post. I've discussed this with the dev team and hopefully the fix will make it into the next SPs for 2005 and 2008 (too late to fix such a rare problem in such a critical component at this stage of 2008 development).
SQL Server experts to answer whatever question you can come up with. Our new SQL Server Forums are
How To Increase Tempdb Size In Sql Server 2008
live! Come on over! We've restricted the ability to create new checkdb repair threads on these forums. SQL Server Forums Profile | ActiveTopics | Members | Search | ForumFAQ Register Now dbcc checktable and get your question answered! Username: Password: Save Password Forgot your Password? All Forums General SQL Server Forums Data Corruption Issues Another Database Consistency Problem on MSDB Reply to Topic http://www.sqlskills.com/blogs/paul/checkdb-bug-that-people-are-hitting-msg-8967-level-16-state-216/ Printer Friendly Author Topic aashirwad Starting Member 9 Posts Posted-07/25/2008: 11:47:48 Friends,This is a SQL Server 2005 database and I get this error when I run DBCC CHECKDB(msdb) WITH ALL_ERRORMSGS, NO_INFOMSGSMsg 8967, Level 16, State 216, Line 1An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services.Msg 8921, Level 16, State 1, Line http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=107429 1Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.The following error messages shows up in SQL Server Error Log:DBCC encountered a page with an LSN greater than the current end of log LSN (1113334:0:1) for its internal database snapshot. Could not read page (0:4072), database 'msdb' (database ID 31), LSN = (6684768:0:0), type = 78, isInSparseFile = 0. Please re-run this DBCC command.DBCC CHECKDB (msdb) WITH all_errormsgs, no_infomsgs executed by sa terminated abnormally due to error state 1. Elapsed time: 0 hours 0 minutes 3 seconds.My SQL Server 2005 is ProductVersion:9.00.3042.00 / SP2 / Standard EditionPlease Help!! GilaMonster Flowing Fount of Yak Knowledge South Africa 4507 Posts Posted-07/25/2008: 12:11:28 Do you have a backup of MSDB?--Gail ShawSQL Server MVP aashirwad Starting Member 9 Posts Posted-07/25/2008: 14:21:37 Yes... I have a backup. Shall I go ahead and import it? sodeep Flowing Fount of Yak Knowledge USA 7174 Posts Posted-07/25/2008: 14:29:09 Also check TempDB space? SQL server uses
SP3 64-bit environment some queries started to complain about the following error in the SQL Server Error Logs.Message:SQL http://ergemp.blogspot.com/2009/05/sql-server-detected-logical-consistency.html Server detected a logical consistency-based I/O error: torn page (expected signature: 0x0; actual signature: 0x5be5d620). It occurred during a read of page (1:718204) in database ID 7 at offset 0x0000015eaf8000 in file 'E:\MSSQL\DATA\SMPPGatewayBEP.mdf'. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens dbcc checkdb 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.It is obvious that there happened an IO inconsistency. Maybe while the backup or the restore operation. I decided to execute DBCC CHECKDB('SMPPGatewayBEP') But the following output generated dbcc checkdb tablock by the server.Msg 8967, Level 16, State 216, Line 1An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services.DBCC results for 'SMPPGatewayBEP'.Msg 8921, Level 16, State 1, Line 1Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.CHECKDB found 0 allocation errors and 0 consistency errors in database 'SMPPGatewayBEP'.After a little search on msdn i decided to try to set the database into the single user mode by running:alter database SMPPGatewayBEP SET SINGLE_USER WITH ROLLBACK IMMEDIATEDBCC CHECKDB('SMPPGatewayBEP', REPAIR_ALLOW_DATA_LOSS)Now it started to repair the data pages by reading the log pages. There is thousands of lines of this output so i put the head and tail part of the output. DBCC results for 'SMPPGatewayBEP'.Service Broker Msg 9675, State 1: Message Types analyzed: 14.Service Broker Msg 9676, State 1: Service Contracts analyzed: 6.Service Broker Msg 9667, State 1: Services analyzed: 3.Service Broker Msg 9668, State 1: Service Queues analyzed: 3.Service Broker Msg 9669, State 1