An inconsistency was detected during an internal operation

Article
07/08/2006

Question

_{Saturday, July 8, 2006 4:45 AM}

I am getting the following error:

Msg 5242, Level 22, State 1, Line 1

An inconsistency was detected during an internal operation in database 'Finance'(ID:5) on page (1:361249). Please contact technical support. Reference number 3.

It is caused by the following simple query:

"SELECT DISTINCT GainStd FROM vPriceParamStdDiscrete WHERE GainStd IS NOT NULL ORDER BY GainStd"

Could anyone offer any tip for fixing this problem?

Thanks in advance!

All replies (10)

_{Saturday, July 8, 2006 5:59 AM ✅Answered | 1 vote}

DIdi you try to run DBCC CHECKDB for the appropiate database ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

_{Tuesday, July 11, 2006 2:00 AM}

Thanks, Jens.

I should have done it, but I was so frustrated that I backed up the database, uninstalled and reinstalled the server (I need to do this anyway because my evaluation copy was expiring and I reinstalled a standard edition). Now, everything is fine.

_{Saturday, December 2, 2006 12:46 AM}

That worked for me.

Weird. All of the sudden it started happening.

Aah!!! It is after all a Microsoft product. What else can you expect?

Thanks

Narayanan

_{Monday, January 1, 2007 8:22 PM}

When I use DBCC CHECKDB to check or repair (with REPAIR_REBUILD), I got An inconsistency was detected during an internal operation in database 'tempdb'

I can't set tempdb database as single user mode, so I can't repair it. What can I do?

_{Thursday, February 12, 2009 8:24 PM}

The same message is usual if I try to run through maintenance package DBCC CHECKDB for user's databases on MS SQL Server 2005 that is installed on VM. I've done some research on the internet and didn't find anything helpful. Database functions normal, error message disappears if I disable DBCC CHECKDB. Restart of the server (DB and/or physical) do not make any difference. It would be nice to find the cause...

_{Friday, February 13, 2009 1:10 AM}

Run DBCC CHECKDB (DatabaseName) WITH ALL_ERRORMSGS, NO_INFOMSGS manually for each of your databases and post the output from the one(s) with errors reported on this post.

There is nothing Normal about this error message. I have 20+ Virtualized SQL Servers, and none of them ever report this error for CHECKDB. This is a sign of corruption and it should be properly investigated and corrected.

Consider using SQLIOSim to test your disk subsystem:

How to use the SQLIOSim utility to simulate SQL Server activity on ...

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

_{Friday, February 13, 2009 9:34 AM}

One other thing to check is the current setting for PAGE_VERIFY for the database. If it is set to NONE, you will get a 5242 when you would otherwise get a 824 if it were set to CHECKSUM or TORN_PAGE_DETECTION while performing SELECT operations from a table with corruption in it.

With PAGE_VERIFY set to NONE, you would get:

Msg 5242, Level 22, State 1, Line 1

An inconsistency was detected during an internal operation in database 'CheckDBPractice'(ID:24) on page (1:114). Please contact technical support. Reference number 6.

With PAGE_VERIFY set to TORN_PAGE_DETECTION, you would get:

Msg 824, Level 24, State 2, Line 1

SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x55555555; actual signature: 0x12c048d5). It occurred during a read of page (1:114) in database ID 24 at offset 0x000000000e4000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestCorruption.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 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.

and with PAGE_VERIFY set to CHECKSUM, you would get:

Msg 824, Level 24, State 2, Line 1

SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xe5582473; actual: 0x1a5f4505). It occurred during a read of page (1:114) in database ID 24 at offset 0x000000000e4000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TestCorruption.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 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.

This can be caused by a number of factors, but in any case, it is a sign of corruption, and data integrity issues with your database. The above error messages are from Tony Rogerson's blog post How to create a corrupt database using BULK INSERT/ UPDATE and BCP - SQL Server as a HEX editor.

What is your PAGE_VERIFY set to currently? If you are on SQL Server 2005, CHECKSUM is the recommended best practice setting for this, which stamps each page with a checksum value when it is written to disk. If the CHECKSUM value stored on the page doesn't match the calculated checksum value when it is read from disk, it will throw a 824 error, with the incorrect checksum, which is a pointer that something is awry with your disk subsystem.

Jonathan Kehayias
http://sqlblog.com/blogs/jonathan_kehayias/
http://www.sqlclr.net/
Please click the Mark as Answer button if a post solves your problem!

_{Friday, February 13, 2009 3:18 PM}

The concern is perfectly valid but the message is about TempDB. All users’ databases are fine (CHECKDB command runs with no errors); disks are fine (this check was my first request to our sysadmins); manual DBCC CHECKDB (tempdb) WITH ALL_ERRORMSGS gives 0 errors; manual run of the same script that runs through maintenance plan is the same but do not return any errors! And tempdb doesn’t allow TORN DETECTION setting other than “NONE”.

Here are part of the log file:

Update Statistics Task (ServerName)

Update Statistics on Local server connection

Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.

Databases: All user databases

Object: Tables and views

All existing statistics

Task start: 2009-02-09T22:24:10.

Task end: 2009-02-09T23:45:10.

Failed:(-1073548784) Executing the query "UPDATE STATISTICS [DatabaseName].[TableName]

WITH FULLSCAN

" failed with the following error: "The operating system returned error stale page (a page read returned a log sequence number (LSN) (4629:256:1) that is older than the last one that was written (0:0:0)) to SQL Server during a read at offset 0x00000025d40000 in file 'c:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf'. 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.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Stack Signature for the dump is 0x11702F0F

External dump process return code 0x20000001.

External dump process returned no errors.

Error: 5242, Severity: 22, State: 1.

An inconsistency was detected during an internal operation in database 'tempdb'(ID:2) on page (1:151826). Please contact technical support. Reference number 5.

Backup Database backed up. Database: [DatabaseName], creation date(time): 2008/11/19(13:05:09), pages dumped: 1851151, first LSN: 574458:39:40, last LSN: 574472:1490:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'c:\backup\ DatabaseName.bak'}). This is an informational message only. No user action is required.

This instance of SQL Server has been using a process ID of 1696 since 12/23/2008 6:24:38 PM (local) 12/23/2008 11:24:38 PM (UTC). This is an informational message only; no user action is required.

This instance of SQL Server has been using a process ID of 1696 since 12/23/2008 6:24:38 PM (local)

12/23/2008 11:24:38 PM (UTC). This is an informational message only; no user action is required.

SQL Server has encountered 2 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [c:\Program Files\Microsoft SQL Server\MSSQL\data\templog.ldf] in database [tempdb] (2). The OS file handle is 0x000007C0. The offset of the latest long I/O is: 0x00000001322000

SQL Server has encountered 3 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [c:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf] in database [tempdb] (2). The OS file handle is 0x00000794. The offset of the latest long I/O is: 0x00000024770000

SQL Server has encountered 27 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [C:\PSFTDATA\Database_1_Data.MDF] in database [Database_1] (7). The OS file handle is 0x000006B0. The offset of the latest long I/O is: 0x000001eaca0000

SQL Server has encountered 3 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [c:\Program Files\Microsoft SQL Server\MSSQL\data\templog.ldf] in database [tempdb] (2). The OS file handle is 0x000007C0. The offset of the latest long I/O is: 0x000000015a0000

SQL Server has encountered 69 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [c:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf] in database [tempdb] (2). The OS file handle is 0x00000794. The offset of the latest long I/O is: 0x00000038ac0000

Operating system error 'stale page (a page read returned a log sequence number (LSN) (4629:256:1) that is older than the last one that was written (0:0:0))' resulted from attempt to read the following: sort run page (1:77472), in file 'c:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf', in database with ID 2. Sort is retrying the read.

Error: 823, Severity: 24, State: 7.

The operating system returned error stale page (a page read returned a log sequence number (LSN) (4629:256:1) that is older than the last one that was written (0:0:0)) to SQL Server during a read at offset 0x00000025d40000 in file 'c:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf'. 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.

Using 'dbghelp.dll' version '4.0.5'

Dump thread - spid = 74, PSS = 0x2AFF5318, EC = 0x432F40E8

User initiated stack dump. This is not a server exception dump.

**Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\SQLDump0014.txt

BEGIN STACK DUMP:

ex_raise2: Exception raised, major=52, minor=42, state=1, severity=22

Input Buffer 128 bytes -

_{Thursday, July 9, 2009 4:49 PM}

Thanx It worked for me with the dbcc but i had to execute it with  REPAIR_ALLOW_DATA_LOSS flag.
I checked the corrected table but no data was lost!

_{Tuesday, July 19, 2011 6:29 PM}

Hi Johnathon, I run a dbcc checkdb noindex with physical_only(because now it is busy hours), I got this error below, any idea if it is hardware issue or not, how to determine it?

The bad thing is my clear backup is last week, not sure if restore from a full backup of now to a different disk will work?

Msg 8967, Level 16, State 216, Line 1

An internal error occurred in DBCC that prevented further processing. Contact Customer Support Services.

DBCC results for xxx.

Warning: NO_INDEX option of checkdb being used. Checks on non-system indexes will be skipped.

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.

Msg 8905, Level 16, State 1, Line 1

Extent (1:11930744) in database ID 6 is marked allocated in the GAM, but no SGAM or IAM has allocated it.

CHECKDB found 1 allocation errors and 0 consistency errors not associated with any single object.

CHECKDB found 1 allocation errors and 0 consistency errors in database xxx.

Connect, code, and grow

Share via

Question

All replies (10)

Share via

An inconsistency was detected during an internal operation

Question

All replies (10)

Additional resources