locked
Is this a Bug? System Assertion Failure On Manual Update Stats SQL 2008 R2 SP2 RRS feed

  • Question

  • System:

    SQL 2008 R2 SP2 Enterprise x64 (10.50.4000.0)

    Windows 2008 R2 Enterprise X64 SP1 (6.1.7601)

    Error Message:

    Updating [dbo].[Bob]
    Location:     statutil.cpp:3225
    Expression:   m_fInitialized && m_statBlob.CbSize() && iKey >= -1 && iKey < m_statBlob.GetHeader()->GetKeyCount()
    SPID:         70
    Process ID:   7848
    Msg 3624, Level 20, State 1, Line 1
    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.

    *************

    Issue:

    On one of my systems I've had failures on two separate databases when updating stats.  Below is the error from one of them.  These databases are quite large (300 GB).  The table for the example below has 1.1 million records.

    The table has 6 stats, updating any of them causes the below failure.  DBCC Runs clean across the database and even checking the table separately.

    Using Google I've found this link although there is no mention of issues with stats http://support.microsoft.com/kb/2728534?wa=wsignin1.0

    Has anyone else experienced this issue?  Does the above "fix" resolve this issue for stats as well?

    Table:

    CREATE TABLE dbo.Bob
    (
    	[UserID] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL		
    	,[AccessLevel] [smallint] NOT NULL
    	,[SchemeID] [int] NOT NULL
    	,[ApplicationName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
    	,CONSTRAINT [PK_bob] PRIMARY KEY CLUSTERED  
    	(
    		[UserID] ASC,
    		[AccessLevel] ASC,
    		[SchemeID] ASC,
    		[ApplicationName] ASC
    	)
    	WITH 
    		(
    			PAD_INDEX  = OFF
    			, STATISTICS_NORECOMPUTE  = OFF
    			, IGNORE_DUP_KEY = OFF
    			, ALLOW_ROW_LOCKS  = ON
    			, ALLOW_PAGE_LOCKS  = ON
    			, FILLFACTOR = 90
    		)
    );
    

    Update#1

    Just tried to compress this table wit DATA_COMPRESSION = PAGE and had the below.  With the DBCC results coming back clean I guess this is looking like a bug and possibly something similar to the one reported above.

    Msg 3624, Level 20, State 1, Line 2

    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.

    Msg 0, Level 20, State 0, Line 0

    A severe error occurred on the current command.  The results, if any, should be discarded.


    • Edited by ThePazzer Monday, November 5, 2012 10:36 AM
    Monday, November 5, 2012 10:24 AM

Answers

  • Got to the bottom of this one.

    One of the other statistics (seems to be a system generated one) on the table, was "corrupt".  Dropping this resolved the issue.

    • Proposed as answer by Naomi N Monday, November 12, 2012 2:04 PM
    • Marked as answer by Shulei Chen Tuesday, November 13, 2012 9:21 AM
    Monday, November 12, 2012 11:21 AM

All replies

  • Can you try rebuild all the indexes of this table and test again if you still see the assertion dumps?
    Monday, November 5, 2012 10:42 AM
  • Assertion are the checks added in SQL Server code to ensure data integrity. In simple terms, we validate certain conditions at many places in code and raise failure if they are not met.

    Location:     statutil.cpp:3225  -> This is file name and line number for support
    Expression:   m_fInitialized && m_statBlob.CbSize() && iKey >= -1 && iKey < m_statBlob.GetHeader()->GetKeyCount() -> This is the condition which should never fail. If this fails, then there is something wrong somewhere and would cause problem if we continue. Hence we stop the execution


    You are running 10.50.4000 which is service pack 2 and KB which you have found fixes one of the issue about statistics. If we look closely at symptoms they are not exactly matching with your assertion. If you want you can give a shot to it.

    To reach to final conclusion, may I ask you to share the dump? For the time being, rebuilding the indexes should fix it.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Monday, November 5, 2012 10:57 AM
  • just to add, DBCC CHECKDB doesn't check for statistics of indexes and hence it's clean.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Monday, November 5, 2012 10:58 AM
  • I tried to rebuild the index which failed and had an assertion error.

    Next I dropped the index (table is now a heap) and tried to update statistics on one of the "auto" stats - this failed with

    Location:  statutil.cpp:3225
    Expression:  m_fInitialized && m_statBlob.CbSize() && iKey >= -1 && iKey < m_statBlob.GetHeader()->GetKeyCount()
    SPID:   391
    Process ID:  7848
    Msg 3624, Level 20, State 1, Line 1
    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.

    Created index and retried - same error.

    Looks like I'm going to have to try the CU with this fix detailed here: http://support.microsoft.com/kb/2728534?wa=wsignin1.0

    Sorry can't provide the minidump.

    I will confirm if this does fix the problem.

    Monday, November 5, 2012 12:13 PM
  • Have applied SQL 2008 R2 SP2 CU3 (this was on a test system) - and the problem still exists. DBCC Checks still come back clean.

    I'm raising this directly with Microsoft and will update as and when.

    Thursday, November 8, 2012 9:58 AM
  • Got to the bottom of this one.

    One of the other statistics (seems to be a system generated one) on the table, was "corrupt".  Dropping this resolved the issue.

    • Proposed as answer by Naomi N Monday, November 12, 2012 2:04 PM
    • Marked as answer by Shulei Chen Tuesday, November 13, 2012 9:21 AM
    Monday, November 12, 2012 11:21 AM
  • Got to the bottom of this one.

    One of the other statistics (seems to be a system generated one) on the table, was "corrupt".  Dropping this resolved the issue.


    Awesome. I could have done that by dump so am curious, how did you identify or this was a trial and error?

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Monday, November 12, 2012 11:22 AM