在CRM2011长时间运行后,发现PrincipalObjectAccess表、AsyncOperationBase表、WorkflowLogBase表产生了上千万条的数据,导致系统运行缓慢。
经过查找处理办法,积累了下面的经验:
微软官方给出的清理办法:https://support.microsoft.com/en-gb/kb/968520
IF EXISTS (SELECT name from sys.indexes
WHERE name = N'CRM_AsyncOperation_CleanupCompleted')
DROP Index AsyncOperationBase.CRM_AsyncOperation_CleanupCompleted
GO
CREATE NONCLUSTERED INDEX CRM_AsyncOperation_CleanupCompleted
ON [dbo].[AsyncOperationBase] ([StatusCode],[StateCode],[OperationType])
GO
while(1=1)
begin
declare @DeleteRowCount int = 10000
declare @rowsAffected int
declare @DeletedAsyncRowsTable table (AsyncOperationId uniqueidentifier not null primary key)
insert into @DeletedAsyncRowsTable(AsyncOperationId)
Select top (@DeleteRowCount) AsyncOperationId from AsyncOperationBase
where
OperationType in (1, 9, 12, 25, 27, 10)
AND StateCode = 3
AND StatusCode in (30, 32)
select @rowsAffected = @@rowcount
delete poa from PrincipalObjectAccess poa
join WorkflowLogBase wlb on
poa.ObjectId = wlb.WorkflowLogId
join @DeletedAsyncRowsTable dart on
wlb.AsyncOperationId = dart.AsyncOperationId
delete WorkflowLogBase from WorkflowLogBase W, @DeletedAsyncRowsTable d
where
W.AsyncOperationId = d.AsyncOperationId
delete BulkDeleteFailureBase From BulkDeleteFailureBase B, @DeletedAsyncRowsTable d
where
B.AsyncOperationId = d.AsyncOperationId
delete WorkflowWaitSubscriptionBase from WorkflowWaitSubscriptionBase WS, @DeletedAsyncRowsTable d
where
WS.AsyncOperationId = d.AsyncOperationID
delete AsyncOperationBase From AsyncOperationBase A, @DeletedAsyncRowsTable d
where
A.AsyncOperationId = d.AsyncOperationId
/*If not calling from a SQL job, use the WAITFOR DELAY*/
if(@DeleteRowCount > @rowsAffected)
return
else
WAITFOR DELAY '00:00:02.000'
end
OperationType参照表:
系统事件 | 1 |
批量电子邮件 | 2 |
导入文件分析 | 3 |
转换分析数据 | 4 |
导入 | 5 |
活动传播 | 6 |
重复检测规则发布 | 7 |
批量重复检测 | 8 |
SQM 数据收集 | 9 |
工作流 | 10 |
快速市场活动 | 11 |
匹配代码更新 | 12 |
批量删除 | 13 |
删除服务 | 14 |
索引管理 | 15 |
收集组织统计信息 | 16 |
导入子进程 | 17 |
计算组织的存储规模 | 18 |
收集组织的数据库统计信息 | 19 |
收集组织规模统计信息 | 20 |
数据库优化 | 21 |
计算组织的最大存储规模 | 22 |
批量删除子进程 | 23 |
更新统计信息的间隔 | 24 |
组织全文目录索引 | 25 |
数据库日志备份 | 26 |
更新合同状态 | 27 |
DBCC SHRINKDATABASE 维护作业 | 28 |
DBCC SHRINKFILE 维护作业 | 29 |
对所有索引维护作业重新编制索引 | 30 |
存储限制通知 | 31 |
清理停用的工作流序集 | 32 |
定期系列扩展 | 33 |
导入示例数据 | 34 |
目标汇总 | 35 |
审核分区创建 | 36 |
检查语言包更新 | 37 |
设置语言包 | 38 |
更新组织数据库 | 39 |
更新解决方案 | 40 |
重新生成实体行计数快照数据 | 41 |
重新生成阅读共享快照数据 | 42 |
公布到 Yammer | 43 |