SQL Server 更新统计信息出现严重错误,应放弃任何可能产生的结果

栏目: 数据库 · 发布时间: 6年前

一台SQL Server 2008 R2版本(具体版本如下所示)的数据库,最近几天更新统计信息的作业出错,错误如下所示:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)

    Jun 28 2012 08:36:30

    Copyright (c) Microsoft Corporation

    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

SQL Server 更新统计信息出现严重错误,应放弃任何可能产生的结果

第一次碰到这么奇葩的问题。查看错误日志,就会发现更新统计信息时出现异常,生成了dump文件。

SQL Server 更新统计信息出现严重错误,应放弃任何可能产生的结果

对表做DBCC CHECKTABLE发现正常,未有一致性错误。

DBCC CHECKTABLE ( 'TBusRetail' )

SQL Server 更新统计信息出现严重错误,应放弃任何可能产生的结果

后面查了一下资料,在官方文档看到有个Bug会导致这个问题,官方文档为:FIX: An access violation may occur when you update the statistics of a table after you enable and then disable conflict detection on a table in in SQL Server 2008 or in SQL Server 2008 R2。刚好我们这个环境的版本也在其中。具体参考下面:

SQL Server 更新统计信息出现严重错误,应放弃任何可能产生的结果

Cause

This issue occurs because the database engine is trying to load dangling statistics. When P2P conflict detection is enabled, an MDColumnIdP2pCdId system column is added to the base index rowset of the table. Replication-related queries may create statistics on the system column automatically. When P2P conflict detection is disabled, the system column is removed from the table. However, the corresponding statistics remain. Therefore, updating statistics causes the access violation exception to occur because the statistics cannot be added to the table.

SQL Server 2008 Service Pack 2

The fix for this issue was first released in Cumulative Update 3 for SQL Server 2008 Service Pack 2. For more information about this cumulative update package, click the following article number to view the article in the Microsoft Knowledge Base:

2498535 Cumulative update package 3 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 fix release. Microsoft recommends 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 The SQL Server 2008 builds that were released after SQL Server 2008 Service Pack 2 was released

Microsoft SQL Server 2008 hotfixes are created for specific SQL Server service packs. You must apply a SQL Server 2008 Service Pack 2 hotfix to an installation of SQL Server 2008 Service Pack 2. By default, any hotfix that is provided in a SQL Server service pack is included in the next SQL Server service pack.

The fix for this issue was first released in Cumulative Update package 6 for SQL Server 2008 R2. 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:

2489376 Cumulative Update package 6 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 The SQL Server 2008 R2 builds that were released after SQL Server 2008 R2 was released

但是等我打上补丁后,测试发现问题依然存在,也只有这个表存在这个问题。后面仔细检查,发现这个表有不少计算列( Computed Column ),刚好以前也遇到过由于计算列导致统计信息更新出现错误的情况,一检查,发现这表有大量的统计信息,遂生成删除统计信息的脚本后执行删除(排除了相关索引的统计信息)。然后再更新统计信息,OK,问题解决了。看来又是神奇计算列导致的统计信息更新异常!

SELECT   'DROP STATISTICS dbo.TBusRetail.' + QUOTENAME ( name ) + ';'

FROM     sys . stats

WHERE    object_id = OBJECT_ID ( 'dbo.TBusRetail' )

DROP STATISTICS dbo.TBusRetail.[PK_TBUSRETAIL];   -- 排除这个统计信息

DROP STATISTICS dbo.TBusRetail.[IdxRefNoOpDate]; -- 排除这个统计信息

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_00000016_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_00000015_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_00000003_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_PayWay_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_Opr_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_Charger_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_RefNo_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_TicketAmount_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_ChargeDate_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_TicketFAmount_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_PayAmount_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_Accepted_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_OpDate_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_Checked_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_CheckDate_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_HangUp_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_TPayAmount_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_Remark_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_RetailType_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_AcceptDate_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_IfNetRetail_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_DisModified_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_Dealed_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_VipScale_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_00000024_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_00000032_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_00000033_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_00000031_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_00000030_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_0000002C_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_0000002B_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_0000002A_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_00000029_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_00000028_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_00000027_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_00000026_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_00000025_58BCECDB]

DROP STATISTICS dbo . TBusRetail . [_WA_Sys_00000018_58BCECDB]

上一次遇到的问题在这里: 消息 8134,级别 16,状态 1,第 1 行 遇到以零作除数错误

参考资料:

https://support.microsoft.com/en-us/help/2498796/fix-an-access-violation-may-occur-when-you-update-the-statistics-of-a


以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

谁排第一

谁排第一

Amy N. Langville、Carl D. Meyer / 郭斯羽 / 机械工业出版社 / 2014-6 / 49

《谁排第一?关于评价和排序的科学》是首个关于评分和排名科学的著作。它是搜索排序姊妹篇的第二本。本书主要内容有:排名概述、梅西法、科利法、基纳法、埃洛体系、马尔可夫法、攻防评分法、基于重新排序的排名方法、分差、用户偏好评分、处理平局、加入权重、“假如……会怎样”的问题与敏感性、排名聚合、比较排名的方法、数据等。 《谁排第一?关于评价和排序的科学》可作为数学、计算机、网络技术、管理学和数据科学等......一起来看看 《谁排第一》 这本书的介绍吧!

html转js在线工具
html转js在线工具

html转js在线工具

UNIX 时间戳转换
UNIX 时间戳转换

UNIX 时间戳转换

HSV CMYK 转换工具
HSV CMYK 转换工具

HSV CMYK互换工具