Microsoft – SQL Server – Error – Error: 17066, Severity: 16, State: 1 // SQL Server Assertion: File: cxrowset.cpp, line=1538 Failed Assertion = ‘pXacc–>FHasVirtualColumn()’. This error may be timing-related

We started getting this “error – Error: 17066, Severity: 16, State: 1:”

Due to its severity level 16 and SQL Server’s suggested remediation steps:

  • Run DBCC CheckDB (to check database for structural integrity)
  • Restart the server (to ensure in-memory data structures are not corrupted)

…. I am thinking I am in for long nights…and not the nice ones; actually the grumpy – I am a DBA ones…

SQL Server Error logs reads —


2013-01-03 01:22:28.12 spid59 Error: 17066, Severity: 16, State: 1.
2013-01-03 01:22:28.12 spid59 SQL Server Assertion: File: <"cxrowset.cpp">, line=1538 Failed Assertion = 'pXacc->FHasVirtualColumn()'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
2013-01-03 01:22:28.12 spid59 Error: 3624, Severity: 20, State: 1.
2013-01-03 01:22:28.12 spid59 A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.
2013-01-03 01:22:28.13 spid65 Using 'dbghelp.dll' version '4.0.5'
2013-01-03 01:22:28.13 spid65 **Dump thread - spid = 0, EC = 0x00000009E92D64B0
2013-01-03 01:22:28.13 spid65 ***Stack Dump being sent to D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\LOG\SQLDump0440.txt
2013-01-03 01:22:28.13 spid65 * *******************************************************************************
2013-01-03 01:22:28.13 spid65 *
2013-01-03 01:22:28.13 spid65 * BEGIN STACK DUMP:
2013-01-03 01:22:28.13 spid65 * 01/03/13 01:22:28 spid 65
2013-01-03 01:22:28.13 spid65 *
2013-01-03 01:22:28.13 spid65 * Location: "cxrowset.cpp":1538
2013-01-03 01:22:28.13 spid65 * Expression: pXacc->FHasVirtualColumn()
2013-01-03 01:22:28.13 spid65 * SPID: 65
2013-01-03 01:22:28.13 spid65 * Process ID: 2036
2013-01-03 01:22:28.13 spid65 *
2013-01-03 01:22:28.13 spid65 * Input Buffer 255 bytes -
2013-01-03 01:22:28.13 spid65 * 16 00 00 00 12 00 00 00 02 00 00 00 00 00 00 00 00 00
2013-01-03 01:22:28.13 spid65 * ÿÿ & 01 00 00 00 ff ff 0d 00 00 00 00 01 26 04 04 00 00 00
2013-01-03 01:22:28.13 spid65 * ç@ Ð 4Ž @ P 0 00 00 00 e7 40 1f 09 04 d0 00 34 8e 05 40 00 50 00 30
2013-01-03 01:22:28.13 spid65 * n v a r c h a r 00 20 00 6e 00 76 00 61 00 72 00 63 00 68 00 61 00 72
2013-01-03 01:22:28.13 spid65 * ( 4 0 0 0 ) , @ P 00 28 00 34 00 30 00 30 00 30 00 29 00 2c 00 40 00 50

Yes, young one, this is not an easy one.

And, you are possibly thinking “If you wanted cryptic error messages, you would have stayed with C++ and be an Old School hacker”

But, anyways as you have friends on the .Net, you have come to the right place.

Couple of things you want to keep in mind:

  1. What is your spid – In our case 65
  2. As spid is 65, quite a bit above 50 we are in user’s land; the session that is failing is not a system process, but a user process
  3. Also keep in mind the process ID, for those running multiple SQL Server Instances on the same server
  4. Then try your best to read through the InputBuffer — It contains every thing you need to tell you which SQL Statement is failing

Our problem was traced back to the day I took that proverbial short-cut:


create table dbo.store
(
	    storeID bigint not null identity(1,1)
	  , storeName sysname not null
	  , partitionIDOrig as 0 persisted

	  ,	CONSTRAINT [PK_Store] PRIMARY KEY NONCLUSTERED
		(
			  [storeID] ASC
			, [partitionIDOrig]
		)

		ON [partitionScheme]([partitionIDOrig ])

)
go

If you take a quick look at our definition for partitionIDOrig, you will see it is hard-coded to be 0.

Everything worked well when we were inserting into the table.

But, once we changed to Merge, things broke.

To fix:


create table dbo.store
(
	    storeID bigint not null identity(1,1)
	  , storeName sysname not null
	  , partitionID as isNull(cast(rand(100) as int), 0) persisted

	  ,	CONSTRAINT [PK_Store] PRIMARY KEY NONCLUSTERED
		(
			  [storeID] ASC
			, [partitionID]
		)

		ON [partitionScheme]([partitionID ])

)
go

Our new partitionID is an actual computed \ variant column:


    partitionID as isNull(cast(rand(100) as int), 0) persisted

 

I think when Microsoft invokes FHasVirtualColumn(), they are asking whether we have Virtual columns — And, things “stumble” a bit when the calculated column is actually a hard-coded value.


References:

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s