SQL Server 更新统计信息出现严重错误,应放弃任何可能产生的结果
2018-10-17 15:00 潇湘隐者 阅读(1912) 评论(0) 编辑 收藏 举报
一台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)
第一次碰到这么奇葩的问题。查看错误日志,就会发现更新统计信息时出现异常,生成了dump文件。
对表做DBCC CHECKTABLE发现正常,未有一致性错误。
DBCC CHECKTABLE('TBusRetail')
后面查了一下资料,在官方文档看到有个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。刚好我们这个环境的版本也在其中。具体参考下面:
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,问题解决了。看来又是神奇的计算列(Computed Column)导致的统计信息更新异常!
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

【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
2016-10-17 Cannot execute as the database principal because the principal "guest" does not exist, this type of principal cannot be impersonated, or you do not have permission.
2013-10-17 The process could not execute 'sp_repldone/sp_replcounters' on 'ServerName'