FIX: Assertion failure or other issues occur when you run a DML query against a table or view that has two indexes in SQL Server 2008 or in SQL Server 2008 R2

Article ID: 2542225 - View products that this article applies to.
Expand all | Collapse all

On This Page

Microsoft distributes Microsoft SQL Server 2008 SP2 or Microsoft SQL Server 2008 R2 fixes as one downloadable file. Because the fixes are cumulative, each new release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 SP2 or SQL Server 2008 R2 fix release.
Back to the top | Give Feedback

SYMPTOMS

Consider the following scenario:
  • You have a table or a view that has two indexes in Microsoft SQL Server 2008 or in Microsoft SQL Server 2008 R2. The first index has an AFTER trigger on it. However, the second index does not have an AFTER trigger on it.
  • Some records in the second index contain version information for rows. For example, you have previously enabled the snapshot isolation feature to enable the row version.
  • You try to run a DML query against the table or against the view.
In this scenario, you may encounter one of the following issues:
  • An assertion failure occurs, and the following error is logged in the SQL Server error log:

    Date Timespid# Error: 17066, Severity: 16, State: 1.
    Date Timespid# SQL Server Assertion: File: pageref.cpp, line=1332 Failed Assertion = '0 == pageFull'. 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.
    Date Timespid# Error: 3624, Severity: 20, State: 1.
    Date Time > spid# 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.
  • The query returns incorrect results.
  • A data corruption issue occurs.
Back to the top | Give Feedback

CAUSE

This issue occurs because of a defect in the SQL Server query optimizer. When you run a DML query that generates a trigger, incorrect row version flags are set, and the SQL Server storage engine uses the old row version information. Therefore, the issues that are described in the "Symptoms" section occur.
Back to the top | Give Feedback

RESOLUTION

Cumulative update information

SQL Server 2008 R2

The fix for this issue was first released in Cumulative Update 10. For more information about how to obtain this cumulative update package for SQL Server 2008 R2, click the following article number to view the article in the Microsoft Knowledge Base:
2591746
(http://support.microsoft.com/kb/2591746/ )
Cumulative Update package 10 for SQL Server 2008 R2
Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
981356
(http://support.microsoft.com/kb/981356/ )
The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 was released

SQL Server 2008 R2 Service Pack 1

The fix for this issue was first released in Cumulative Update 3 for SQL Server 2008 R2 Service Pack 1. For more information about how to obtain this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:
2591748
(http://support.microsoft.com/kb/2591748/ )
Cumulative Update package 3 for SQL Server 2008 R2 Service Pack 1
Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 R2 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2567616
(http://support.microsoft.com/kb/2567616/ )
The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 Service Pack 1 was released

SQL Server 2008 Service Pack 2 (SP2) cumulative update package 6

The fix for this issue was first released in Cumulative Update 6. For more information about how to obtain this cumulative update package for SQL Server 2008 Service Pack 2, click the following article number to view the article in the Microsoft Knowledge Base:
2582285
(http://support.microsoft.com/kb/2582285/ )
Cumulative update package 6 for SQL Server 2008 Service Pack 2
Note Because the builds are cumulative, each new fix release contains all the hotfixes and all the security fixes that were included with the previous SQL Server 2008 Service Pack 2 fix release. We recommend that you consider applying the most recent fix release that contains this hotfix. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
2402659
(http://support.microsoft.com/kb/2402659/ )
The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 2 was released
Back to the top | Give Feedback

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Back to the top | Give Feedback

Properties

Article ID: 2542225 - Last Review: October 17, 2011 - Revision: 3.0
APPLIES TO
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 R2 Enterprise
Keywords: 
kbqfe kbfix kbexpertiseadvanced kbsurveynew KB2542225
Back to the top | Give Feedback

Give Feedback

 
Back to the topBack to the top