1. How to fix these errors?
2. Are the records in the DB still good?
3. How to prevent this happening again?
The database server is running Windows 2000 sp4 and SQL 2000 sp3. It's running on the RAID5 in MS Cluster environment.
I really appreciate your assistance. Thank you.
The error message samples are:
Msg 8974, Sev 16: Text node referenced by more than one node. Object ID 837578022, text, ntext, or image node page (1:130912), slot 27, text ID 25207869603840 is pointed to by page (1:1392667), slot 2 and by page (1:1210230), slot 10. [SQLSTATE 42000]
Msg 8961, Sev 16: Table error: Object ID 837578022. The text, ntext, or image node at page (1:130912), slot 30, text ID 25199404974080 does not match its reference from page (1:131226), slot 17. [SQLSTATE 42000]
Msg 8974, Sev 16: Text node referenced by more than one node. Object ID 837578022, text, ntext, or image node page (1:130912), slot 30, text ID 25254380437504 is pointed to by page (1:1392667), slot 4 and by page (1:131226), slot 17. [SQLSTATE 42000]
Msg 8964, Sev 16: Table error: Object ID 837578022. The text, ntext, or image node at page (1:134162), slot 23, text ID 25204615217152 is not referenced. [SQLSTATE 42000]
Msg 8990, Sev 16: CHECKDB found 0 allocation errors and 211 consistency errors in table 'TABLE_ONE' (object ID 837578022). [SQLSTATE 01000]
Msg 2536, Sev 16: DBCC results for 'TABLE_TWO'. [SQLSTATE 01000]
Msg 8974, Sev 16: Text node referenced by more than one node. Object ID 853578079, text, ntext, or image node page (1:129320), slot 38, text ID 25496001314816 is pointed to by page (1:239021), slot 101 and by page (1:487008), slot 100. [SQLSTATE 42000]
Msg 8961, Sev 16: Table error: Object ID 853578079. The text, ntext, or image node at page (1:129320), slot 39, text ID 25199327903744 does not match its reference from page (1:487008), slot 101. [SQLSTATE 42000]
Msg 8974, Sev 16: Text node referenced by more than one node. Object ID 853578079, text, ntext, or image node page (1:129320), slot 39, text ID 25496001642496 is pointed to by page (1:239021), slot 102 and by page (1:487008), slot 101. [SQLSTATE 42000]
Msg 8964, Sev 16: Table error: Object ID 853578079. The text, ntext, or image node at page (1:136454), slot 1, text ID 25031875559424 is not referenced. [SQLSTATE 42000]
Msg 8964, Sev 16: Table error: Object ID 853578079. The text, ntext, or image node at page (1:136454), slot 2, text ID 25031875887104 is not referenced. [SQLSTATE 42000]
Msg 8964, Sev 16: Table error: Object ID 853578079. The text, ntext, or image node at page (1:136603), slot 2, text ID 25031875231744 is not referenced. [SQLSTATE 42000]
Msg 8928, Sev 16: Object ID 853578079, index ID 255: Page (1:137476) could not be processed. See other errors for details. [SQLSTATE 42000]
Msg 8939, Sev 16: Table error: Object ID 853578079, index ID 255, page (1:137476). Test (m_freeCnt == freeCnt) failed. Values are 357 and 525. [SQLSTATE 42000]
Msg 2593, Sev 16: There are 1972569 rows in 14051 pages for object 'TABLE_TWO'. [SQLSTATE 01000]
Msg 8990, Sev 16: CHECKDB found 0 allocation errors and 166 consistency errors in table 'TABLE_TWO' (object ID 853578079). [SQLSTATE 01000]"
I want to automate the dbcc checkdb process. I create a temp table called #CheckDbTbl and run the following command:
INSERT INTO #CheckDBTbl dbcc checkdb(MyDbName) with tableresults
I plan to send myself an email if any problems are found.
Does anyone know what Error numbers or Levels or anything else I should look for in the #CheckDBTbl that will tell me a problem exists? Right now I'm only checking for: Level >= 16.
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 8999: [Microsoft][ODBC SQL Server Driver][SQL Server]Database tempdb allocation errors prevent further CHECKDB processing.
I get this error above when running a Checkdb on tempDB I haven't found out any fixes yet. I bounced the server service and the job runs, but after 2 days it fails again. Any ideas?
When running DBCC CHECKDB with Repair_Rebuild option in sigle use mode I get 16 consistency error. the error message is always on object ID 2. Can someone tell what is Object ID 2 and what can be done to re-reference what is not referenced? The log follow!
Thanks!
Msg 8964, Sev 16: Table error: Object ID 2. The text, ntext, or image node at page (1:2027787), slot 8, text ID 37039898624 is not referenced. [SQLSTATE 42000] Msg 8964, Sev 16: Table error: Object ID 2. The text, ntext, or image node at page (1:2027787), slot 10, text ID 37039964160 is not referenced. [SQLSTATE 42000] Msg 8964, Sev 16: Table error: Object ID 2. The text, ntext, or image node at page (1:2027787), slot 12, text ID 37040029696 is not referenced. [SQLSTATE 42000] Msg 8964, Sev 16: Table error: Object ID 2. The text, ntext, or image node at page (1:2027787), slot 13, text ID 37040095232 is not referenced. [SQLSTATE 42000] Msg 8964, Sev 16: Table error: Object ID 2. The text, ntext, or image node at page (1:4142208), slot 6, text ID 35910909952 is not referenced. [SQLSTATE 42000] Msg 2536, Sev 16: DBCC results for 'Datawarehouse'. [SQLSTATE 01000] Msg 2536, Sev 16: DBCC results for 'sysobjects'. [SQLSTATE 01000] Msg 2593, Sev 16: There are 1377 rows in 30 pages for object 'sysobjects'. [SQLSTATE 01000] Msg 2536, Sev 16: DBCC results for 'sysindexes'. [SQLSTATE 01000] Msg 8923, Sev 16: The repair level on the DBCC statement caused this repair to be bypassed. [SQLSTATE 01000] Msg 8923, Sev 16: The repair level on the DBCC statement caused this repair to be bypassed. [SQLSTATE 01000] Msg 8923, Sev 16: The repair level on the DBCC statement caused this repair to be bypassed. [SQLSTATE 01000] Msg 8923, Sev 16: The repair level on the DBCC statement caused this repair to be bypassed. [SQLSTATE 01000] Msg 8964, Sev 16: Table error: Object ID 2. The text, ntext, or image node at page (1:4142208), slot 8, text ID 35910975488 is not referenced. [SQLSTATE 42000] Msg 8964, Sev 16: Table error: Object ID 2. The text, ntext, or image node at page (1:4142208), slot 10, text ID 35911041024 is not referenced. [SQLSTATE 42000] Msg 8964, Sev 16: Table error: Object ID 2. The text, ntext, or image node at page (1:4142208), slot 11, text ID 35911106560 is not referenced. [SQLSTATE 42000] Msg 8964, Sev 16: Table error: Object ID 2. The text, ntext, or image node at page (1:4142208), slot 12, text ID 35911172096 is not referenced. [SQLSTATE 42000] Msg 8964, Sev 16: Table error: Object ID 2. The text, ntext, or image node at page (1:4142208), slot 14, text ID 35911237632 is not referenced. [SQLSTATE 42000] Msg 8964, Sev 16: Table error: Object ID 2. The text, ntext, or image node at page (1:7691690), slot 1, text ID 37040160768 is not referenced. [SQLSTATE 42000] Msg 8964, Sev 16: Table error: Object ID 2. The text, ntext, or image node at page (1:7691690), slot 3, text ID 37040226304 is not referenced. [SQLSTATE 42000] Msg 8964, Sev 16: Table error: Object ID 2. The text, ntext, or image node at page (1:7691691), slot 1, text ID 37040291840 is not referenced. [SQLSTATE 42000] Msg 8923, Sev 16: The repair level on the DBCC statement caused this repair to be bypassed. [SQLSTATE 01000] Msg 8923, Sev 16: The repair level on the DBCC statement caused this repair to be bypassed. [SQLSTATE 01000] Msg 8923, Sev 16: The repair level on the DBCC statement caused this repair to be bypassed. [SQLSTATE 01000] Msg 8923, Sev 16: The repair level on the DBCC statement caused this repair to be bypassed. [SQLSTATE 01000] Msg 8923, Sev 16: The repair level on the DBCC statement caused this repair to be bypassed. [SQLSTATE 01000] Msg 8923, Sev 16: The repair level on the DBCC statement caused this repair to be bypassed. [SQLSTATE 01000] Msg 8923, Sev 16: The repair level on the DBCC statement caused this repair to be bypassed. [SQLSTATE 01000] Msg 8923, Sev 16: The repair level on the DBCC statement caused this repair to be bypassed. [SQLSTATE 01000] Msg 8923, Sev 16: The repair level on the DBCC statement caused this repair to be bypassed. [SQLSTATE 01000] Msg 8964, Sev 16: Table error: Object ID 2. The text, ntext, or image node at page (1:7691691), slot 2, text ID 37040357376 is not referenced. [SQLSTATE 42000] Msg 8964, Sev 16: Table error: Object ID 2. The text, ntext, or image node at page (1:7699783), slot 1, text ID 35911303168 is not referenced. [SQLSTATE 42000] Msg 8964, Sev 16: Table error: Object ID 2. The text, ntext, or image node at page (1:7792736), slot 2, text ID 35911368704 is not referenced. [SQLSTATE 42000] Msg 8923, Sev 16: The repair level on the DBCC statement caused this repair to be bypassed. [SQLSTATE 01000] Msg 8923, Sev 16: The repair level on the DBCC statement caused this repair to be bypassed. [SQLSTATE 01000] Msg 8923, Sev 16: The repair level on the DBCC statement caused this repair to be bypassed. [SQLSTATE 01000] Msg 2593, Sev 16: There are 3487 rows in 218 pages for object 'sysindexes'. [SQLSTATE 01000] Msg 8990, Sev 16: CHECKDB found 0 allocation errors and 16 consistency errors in table 'sysindexes' (object ID 2). [SQLSTATE 01000]
When I run either "DBCC CHECKDB WITH NO_INFOMSGS" or using "check database integrity" in maintenance plan, I always got an error below:
The In-row data RSVD page count for object "tablename", index ID 0, partition ID 76911687696384, alloc unit ID 76911687696384 (type In-row data) is incorrect. Run DBCC UPDATEUSAGE. CHECKDB found 0 allocation errors and 1 consistency errors in table 'tablename' (object ID 1173579219). CHECKDB found 0 allocation errors and 1 consistency errors in database 'dbname'.
During the night I run a checkdb that reported a error in tempdb, log down in post for more info. Table error: Object ID 1109769969, index ID 0, partition ID 72057595983888384, alloc unit ID 72057596039200768 (type LOB data). The off-row data node at page (6:459), slot 0, text ID 296853831680 is not referenced. If run a checkdb know it€™s not finding any problem. I€™m bit confused what to do restart the server to get new tempdb? I check with for SQL Server 2005 Books Online for the €śMSSQLSERVER_8974€? error, it tells me to look after a €śLook for Hardware Failure€?. In the sql server log its stands that its have found 82 errors DBCC CHECKDB (tempdb) executed by AIR5MSSQLService found 82 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 3 seconds. Directly after this a stackdump are generated. Somewhere in the middle of my dbcc script. After the i get the result of the next databases. DBCC CHECKDB (model) executed by AIR5MSSQLService found 0 errors and repaired 0 errors. Elapsed time: 0 hours 0 minutes 0 seconds. This is compress version of the log of dbcc job NEW COMPONENT OUTPUT Microsoft(R) Server Maintenance Utility (Unicode) Version 9.0.3042 Report was generated on "FLEFSE". Maintenance Plan: UnderhĂĄll Duration: 00:12:55 Status: Warning: One or more tasks failed.. Details: Check Database Integrity (FLEFSE) Execute TSQL on Local server connection Execution time out: 1800 Task start: 2007-12-05T02:00:01. Task end: 2007-12-05T02:12:57. Failed-1073548784) Executing the query "DECLARE AllDatabases CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT name FROM sys.databases WHERE state_desc='ONLINE' OPEN AllDatabases DECLARE @DBNameVar NVARCHAR(128) DECLARE @Statement NVARCHAR(300) FETCH NEXT FROM AllDatabases INTO @DBNameVar WHILE (@@FETCH_STATUS = 0) BEGIN PRINT N'CHECKING DATABASE ' + @DBNameVar SET @Statement = N'USE ' + @DBNameVar + CHAR(13) + N'DBCC CHECKDB (' + @DBNameVar + N')' EXEC sp_executesql @Statement PRINT CHAR(13) + CHAR(13) FETCH NEXT FROM AllDatabases INTO @DBNameVar END CLOSE AllDatabases DEALLOCATE AllDatabases " failed with the following error: "Table error: Object ID 1109769969, index ID 0, partition ID 72057595983888384, alloc unit ID 72057596039200768 (type LOB data). The off-row data node at page (6:459), slot 0, text ID 296853831680 is not referenced. Table error: Object ID 1109769969, index ID 0, partition ID 72057595983888384, alloc unit ID 72057596039200768 (type LOB data). The off-row data node at page (6:459), slot 1, text ID 296853897216 is not referenced. ... to ... Table error: Object ID 1109769969, index ID 0, partition ID 72057595983888384, alloc unit ID 72057596039200768 (type LOB data). The off-row data node at page (6:459), slot 81, text ID 296871985152 is not referenced. CHECKING DATABASE master DBCC results for 'master'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'. DBCC results for 'mssqlsystemresource'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'mssqlsystemresource'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE tempdb DBCC results for 'tempdb'. .... removed lines ... CHECKDB found 0 allocation errors and 82 consistency errors in table '#4225BEF1' (object ID 1109769969). .... removed lines ... CHECKDB found 0 allocation errors and 82 consistency errors in database 'tempdb'. repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (tempdb). DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE model DBCC results for 'model'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'model'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE msdb DBCC results for 'msdb'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'msdb'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE ReportServer DBCC results for 'ReportServer'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'ReportServer'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE ReportServerTempDB DBCC results for 'ReportServerTempDB'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'ReportServerTempDB'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE caisqlp DBCC results for 'caisqlp'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'caisqlp'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE BKALKYL DBCC results for 'BKALKYL'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'BKALKYL'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE distribution DBCC results for 'distribution'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'distribution'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE DMVstatsDB DBCC results for 'DMVstatsDB'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'DMVstatsDB'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE BALDATA DBCC results for 'BALDATA'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'BALDATA'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE VirtualCenterDB DBCC results for 'VirtualCenterDB'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'VirtualCenterDB'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE BINGE01 DBCC results for 'BINGE01'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'BINGE01'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE BMATINK DBCC results for 'BMATINK'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'BMATINK'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE BORDE01 DBCC results for 'BORDE01'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'BORDE01'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE BSTATISTIK DBCC results for 'BSTATISTIK'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'BSTATISTIK'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE MPSmartDB DBCC results for 'MPSmartDB'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'MPSmartDB'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE SmarTeamPilot DBCC results for 'SmarTeamPilot'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'SmarTeamPilot'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE STATISTIK DBCC results for 'STATISTIK'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'STATISTIK'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE VBAS DBCC results for 'VBAS'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'VBAS'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE MDataMM_0F DBCC results for 'MDataMM_0F'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'MDataMM_0F'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE DLDB DBCC results for 'DLDB'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'DLDB'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. CHECKING DATABASE ILDB DBCC results for 'ILDB'. .... removed lines ... CHECKDB found 0 allocation errors and 0 consistency errors in database 'ILDB'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. Command:USE master; GO SET NOCOUNT ON; GO DECLARE AllDatabases CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT name FROM sys.databases WHERE state_desc=''ONLINE'' OPEN AllDatabases DECLARE @DBNameVar NVARCHAR(128) DECLARE @Statement NVARCHAR(300) FETCH NEXT FROM AllDatabases INTO @DBNameVar WHILE (@@FETCH_STATUS = 0) BEGIN PRINT N''CHECKING DATABASE '' + @DBNameVar SET @Statement = N''USE '' + @DBNameVar + CHAR(13) + N''DBCC CHECKDB ('' + @DBNameVar + N'')'' EXEC sp_executesql @Statement PRINT CHAR(13) + CHAR(13) FETCH NEXT FROM AllDatabases INTO @DBNameVar END CLOSE AllDatabases DEALLOCATE AllDatabases GO SET NOCOUNT OFF; GO
This is the top of the stack-dump ===================================================================== BugCheck Dump =====================================================================
This file is generated by Microsoft SQL Server version 9.00.3042.00 upon detection of fatal unexpected error. Please return this file, the query or program that produced the bugcheck, the database and the error log, and any other pertinent information with a Service Request.
Computer type is AT/AT COMPATIBLE. Current time is 02:00:08 12/05/07. 8 Unknown CPU 9., 2333 Mhz processor (s). Windows NT 5.2 Build 3790 CSD Service Pack 2.
Memory MemoryLoad = 98% Total Physical = 8189 MB Available Physical = 163 MB Total Page File = 9795 MB Available Page File = 1527 MB Total Virtual = 8388607 MB Available Virtual = 8373732 MB
DBCC RESULTS --------------------
... lines removed .... **Dump thread - spid = 107, PSS = 0x00000000EE497BC0, EC = 0x00000000EE497BD0 ***Stack Dump being sent to C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGSQLDump0009.txt * ******************************************************************************* * * BEGIN STACK DUMP: * 12/05/07 02:00:08 spid 107 * * DBCC database corruption * * Input Buffer 510 bytes - * DECLARE AllDatabases CURSOR LOCAL FAST_FORWARD READ_ONLY FOR * SELECT name FROM sys.databases WHERE state_desc='ONLINE' OPEN AllDatab * ases DECLARE @DBNameVar NVARCHAR(128) DECLARE @Statement NVARCHAR(30 * 0) FETCH NEXT FROM AllDatabases INTO @DBNameVar WHILE (@@FETCH_STATU * S = 0) BEGIN PRINT N'CHECKING DATABASE ' + @DBNameVar SET @Stat * ement = N'USE ' + @DBNameVar + CHAR(13) + N'DBCC CHECKDB (' + @DB * NameVar + N')' EXEC sp_executesql @Statement PRINT CHAR(13) + C * HAR(13) FETCH NEXT FROM AllDatabases INTO @DBNameVar END CLOSE A * llDatabases DEALLOCATE AllDatabases * * ******************************************************************************* * ------------------------------------------------------------------------------- * Short Stack Dump PSS @0x00000000EE497BC0 -----------------------
My DBCC script looks like this USE master; GO SET NOCOUNT ON; GO DECLARE AllDatabases CURSOR LOCAL FAST_FORWARD READ_ONLY FOR SELECT name FROM sys.databases WHERE state_desc='ONLINE' OPEN AllDatabases DECLARE @DBNameVar NVARCHAR(128) DECLARE @Statement NVARCHAR(300) FETCH NEXT FROM AllDatabases INTO @DBNameVar WHILE (@@FETCH_STATUS = 0) BEGIN PRINT N'CHECKING DATABASE ' + @DBNameVar SET @Statement = N'USE ' + @DBNameVar + CHAR(13) + N'DBCC CHECKDB (' + @DBNameVar + N')' EXEC sp_executesql @Statement PRINT CHAR(13) + CHAR(13) FETCH NEXT FROM AllDatabases INTO @DBNameVar END CLOSE AllDatabases DEALLOCATE AllDatabases GO SET NOCOUNT OFF; GO
we've been having this ancient database with old accounting data running in suspect mode since as long as I can remember (I started working here a year ago), and finally I had some time on my hands so I thought I'd try to get it online again. However I'm running in to problems:
DBCC CHECKDB (myDBName) gives this error: Msg 926, Level 14, State 1, Line 1 Database 'myDBName' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information.
Running sp_helpdb only does not display the suspect database and sp_helpdb 'myDBName' gives this error even though I'm a system administrator: No permission to access database 'myDBName'.
It's possible that I might be able to dig up a backup but that would be quite tedious. Is it possible to bring the database to a state where I'm able to do a CHECKDB at least...?
-- Lumbago "Real programmers don't document, if it was hard to write it should be hard to understand"
every few days i'm getting an error in the application log , eventID 824, saying 'SQL Server detected a logical consistency-based i/o error; incorrect checksum. it then recommends completing a full database consistency check (dbcc checkdb).
i have run the dbcc checkdb ('databasename') and it is returning an error msg of:
Msg 8697, Level 16, State 215, Line 1 An internal error occurred in DBCC which prevented further processing Msg 8921, Level 16, State 1, Line 1 Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors
i have oodles of disk space left. any ideas on where to go from here.
We recently migrated our production server from SQL 2005 (Standard) on Win2003(32-bit) to SQL2012 (Standard; v11.0.3000) on Win2008-R2(64bit). Single-server Dell R510 with 1.2TB storage. Everything went smoothly; the only nagging issue remaining is failure of our maintenance jobs. I tracked the issue down to failure of DBCC CHECKDB. Specifically, the error is:Executed as user: NT SERVICESQLSERVERAGENT. The database could not be exclusively locked to perform the operation. [SQLSTATE 42000] (Error 5030) 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 lockedI have Googled this issue and read extensively. For instance, informative blogs (albeit dated) such as these by Paul Randal (Managing
CheckDB by default takes an internal DB snapshot to get the consistent, point-in-time view of the DB that it needs. If that snapshot creation fails, then it will try to get an exclusive database lock before proceeding (same as if you had executed DBCC CHECKDB WITH TABLOCK). The root problem is not that the lock could not be obtained, it's that the internal database snapshot could not be created. msdn.microsoft.com/en-us/library/ms188796.aspx details the specific situations when an internal database snapshot is not created and table locking is attempted.Â
 I have verified the SQLSERVERAGENT service account has full permissions on the SQLDATA directory where the databases reside and has full permissions on each database within the directory. Just for giggles, I created a job (run as SQLSERVERAGENT) that creates and then deletes a text file in the SQLDATA directory. It runs fine.
Also testedI get the snapshot creation error when manually running DBCC CHECKDB against any of our databases and when executing under a variety of administrator accounts that are members of the SQL sysadmin role and the Domain Admins security group (the Domain Admins is a member of the local Administrators group that has full permissions on all SQL directories/folders).
Additionally, the databases in question are small (200MB to 6GB) and the disk has plenty of elbow room (978GB free on 1.22TB RAID5 array) to create the internal database snapshots. CHECKDB doesn't surface an error message that is detailed enough to determine the precise cause of the error. Any example successfully running DBCC CHECKDB on the SQL2012 (Standard) on a Win2008 R2 (64-bit) server.
I followed the advice of Paul Randal, but Im stumped as I am not able to determin what the corruption issues are. This is SQL 2000 and the database is a Solomon database that was recently upgraded to 6.5. the error I get when running the DBCC checkdb is as follows:
Server: Msg 8966, Level 16, State 1, Line 1 Could not read and latch page (1:18645) with latch type SH. sysindexes failed. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Now, the bad news. I am a bit of a novice and have picked this up from someone who left my company. It appears the latch error has been around for some time and only reared up when I instituted a new back up system that runs a dbcc check befor backing up. I don't think I have any clean backups.
I have a few extremely large databases in SQL Server 6.5 sp3 (soon to be 5a - but we won't talk about that!!) NT 4.0 sp4 (about 10 GIG each). I don't have a big window of down-time in order to do any maintenance. Does anyone know of a way to be able to run dbcc checkdb or other dbcc's that I can run to verify the database yet complete within a reasonable amount of time? The last time dbcc checkdb was run, it was started Friday night and still not complete Sunday night. Over a weekend, I may have up to a 24 hour maintenance window.
Any suggestions would be appreciated. Thanks! Toni
Hai , When I ran DBCC CHECK DB of userdatabase, its reporting along with usual messages as Descriptor for system table '8' in database '8' not found in the descriptor hash table. I could'nt understand being familiar error encounterd . Any one will appreciate for the help
I recently took over a SQL server with 300 MB of data. I am relatively new to SQL 6.5 and have been reading that DBCC checkdb and checkalloc should be run at least once per week. Apparently the person before me never ran any of those checks. Is not running the database consistency checks for so long going to present a problem? has anyone run into problems when running those checks? Any advise is greatly appreciated.
I ran "dbcc checkdb(MCMSdb) with no_infomsgs" and I get the following: Server: Msg 8946, Level 16, State 12, Line 2 Table error: Allocation page (1:274992) has invalid PFS_PAGE page header values. Type is 0. Check type, object ID and page ID on the page.
What cane be done to correct this problem? Can this error prevent a user from connecting to the database?
I ran checkdb and found 4 error message on the db.. it seen like same object.. can anyone tell me what it is.. and how can i fix it? thanks !!!!
1. Server: Msg 8976, Level 16, State 1, Line 35 Table error: Object ID 2094630505, index ID 1. Page (1:809859) was not seen in the scan although its parent (1:77885) and previous (1:809767) refer to it. Check any previous errors. 2. Server: Msg 8978, Level 16, State 1, Line 35 Table error: Object ID 2094630505, index ID 1. Page (1:809860) is missing a reference from previous page (1:809859). Possible chain linkage problem. 3. Server: Msg 8976, Level 16, State 1, Line 35 Table error: Object ID 2094630505, index ID 1. Page (1:1453795) was not seen in the scan although its parent (1:1453347) and previous (1:1453796) refer to it. Check any previous errors. 4. Server: Msg 8978, Level 16, State 1, Line 35 Table error: Object ID 2094630505, index ID 1. Page (1:1453801) is missing a reference from previous page (1:1453795). Possible chain linkage problem.
Does anybody know if the results of DBCC CHECKDB are stored anywhere? If yes, where? Also, if you don't select "attempt to repair minor problems" option when you set up the maintenance plan, will SQL Server let you know about any errors Integrity check encounters? If yes, where the erros can be found?
in the SQL 6.5 documentation it says when running the DBCC CHECKDB, you should make the database read Only or DBO use only. Do you guys know if SQL 6.5 locks rows while this runs? In SQL 7.0/2000 it only locks the schema.
I am using windows nt40 and sql server 6.5 on a DEC ALPHA and accidentlly started a dbcc checkdb. Is it possible to stop the process with out damaging the database?
When running dbcc checkdb from my workstation(nt) I recieve some of the output and then I get "Connection Broken" this is on a 6.5 machine with the service pack 5, what could be causing my ODBC connection to drop during the proccess of running checkdb?
How can I make the dbcc checkdb fail so I can get the errors displayed in the report log? DBCC Checkdb is the step in the integrity job that I run once a month. What I am trying to do is when the dbcc checkdb fails for any reason, I want to get notified so I can correct the problem. I don't want to use repair fast or any other repair parameters that you can select when you run dbcc checkdb function because most of my dbs are 24x7.
I have a job set up that runs the dbcc checkdb and puts the results into a table. Then I run a query against this table to check the results of dbcc checkdb. If there are errors, I get a page that lets me know that there are some problems. When I ran this job on my production server, the job failed stating that there are problems with data integrity. So I copied this db to a dev server and recreated the job just like I have in prod. The job completed successfully withou any errors. Can anybody tell me what to look at to figure out why it failed on the prod server?
Madhur writes "Last week DBCC CHECKDB <database name> took 2 Hrs to complete on a database. Today again i have started but with the following command: dbcc checkdb (<database name>, REPAIR_FAST ).
It is now running for more than 2.5 Hrs now.
Does the execution time increases even when the DB is consistent?
Can we cancel the execution in the middle? What consequences it may have on the db?
I have a db that is about 50Gigs. Last night it went into a suspect mode and I have not been able to get any of my tables to display. I am running the dbcc checkdb. I have been running it for over 7 hrs. Do you have any suggestions as to what I should do or do you have any ideas as to how long this process might take?
Ricardo writes "We are consistently getting this error. We have check the hardware and nothing appears in the checkdk, can you help.
Thanks
Ricardo
dbcc checkdb (virtualrequest) DBCC results for 'tblVRAnswers'. Server: Msg 8928, Level 16, State 1, Line 1 Object ID 370256524, index ID 3: Page (1:243518) could not be processed. See other errors for details. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 370256524, index ID 3, page (1:243518). Test (IS_ON (BUF_IOERR, bp->bstat) &&bp->berrcode) failed. Values are 2057 and -1. There are 9608 rows in 106 pages for object 'tblVRAnswers'.
CHECKDB found 0 allocation errors and 2 consistency errors in database 'VirtualRequest'.
dbcc checkdb (virtualrequest) DBCC results for 'tblvrquestions'. Server: Msg 8952, Level 16, State 1, Line 1 Table error: Database 'VirtualRequest', index 'tblVRRequestedItems.IX_NC_tblVRRequestedItems_CurrentStatusID' (ID 784825958) (index ID 2). Extra or invalid key for the keys: Server: Msg 8956, Level 16, State 1, Line 1 Index row (1:214293:372) with values (CurrentStatusID = 3 and RequestedItemID = 413260) points to the data row identified by (). There are 6614 rows in 103 pages for object 'tblvrquestions'.
CHECKDB found 0 allocation errors and 1 consistency errors in database 'VirtualRequest'
dbcc checkdb (virtualrequest) DBCC results for 'tblvrquestions'. Server: Msg 8952, Level 16, State 1, Line 1 Table error: Database 'VirtualRequest', index 'tblVRRequestedItems.IX_NC_tblVRRequestedItems_CurrentStatusID' (ID 784825958) (index ID 2). Extra or invalid key for the keys: Server: Msg 8956, Level 16, State 1, Line 1 Index row (1:214293:372) with values (CurrentStatusID = 3 and RequestedItemID = 413260) points to the data row identified by (). There are 6614 rows in 103 pages for object 'tblvrquestions'.
CHECKDB found 0 allocation errors and 1 consistency errors in database 'VirtualRequest'."
My nightly maintenance task runs fine normally. Last night it didn't. When it got to DBCC CHECKDB for an audit database (it gets populated from triggers in other databases; one of the tables has over a million records), the live database server shut down and did not come back up. DBCC CHECKDB completed in 6 seconds with no errors. The next task in the sequence that would have run (but the log has no message of it completing or failing) was DBCC INDEXDEFRAG on that audit table with over a million records.
Even the OFF button on the 64-bit Dell server (SQL2005, Windows Server Enterprise x64 2003 SP1, 16 gig ram) did not work. There wasn't even the blue screen of death. It was just a blue screen. A plug out and plug back in was required.
The only recent configuration change has been that we have had Dell install a piece of disk-level replication software called Double Take.
When i run TKizers sp to check database integrity. I'm getting this error.
Executed as user: IAPESQLAdmin. ...536) DBCC results for 'sysobjects'. [SQLSTATE 01000] (Message 2536) There are 1364 rows in 23 pages for object 'sysobjects'. [SQLSTATE 01000] (Message 2593) DBCC results for 'sysindexes'. [SQLSTATE 01000] (Message 2536) There are 138 rows in 5 pages for object 'sysindexes'. [SQLSTATE 01000] (Message 2593) DBCC results for 'syscolumns'. [SQLSTATE 01000] (Message 2536) There are 5251 rows in 104 pages for object 'syscolumns'. [SQLSTATE 01000] (Message 2593) DBCC results for 'systypes'. [SQLSTATE 01000] (Message 2536) There are 26 rows in 1 pages for object 'systypes'. [SQLSTATE 01000] (Message 2593) DBCC results for 'syscomments'. [SQLSTATE 01000] (Message 2536) There are 2115 rows in 1112 pages for object 'syscomments'. [SQLSTATE 01000] (Message 2593) DBCC results for 'sysfiles1'. [SQLSTATE 01000] (Message 2536) There are 2 rows in 1 pages for object 'sysfiles1'. [SQLSTATE 01000] (Message 2593) DBCC results for 'syspermissions'. [SQLSTATE... The step failed.
I ran "dbcc checkdb(MCMSdb) with no_infomsgs" and I get the following:Server: Msg 8946, Level 16, State 12, Line 2Table error: Allocation page (1:274992) has invalid PFS_PAGE pageheader values. Type is 0. Check type, object ID and page ID on thepage.What cane be done to correct this problem?Can this error prevent a user from connecting to the database?
