Insight in AsyncOperationBase needed?
If you're asyncoperationbase has grown beyond 1.000.000 records, then you most likely want to clean this up for performance reasons.
You can just jump to the Microsoft KB article and run some scripts to clean this up, but you might also want to do some more research upfront to avoid getting into this situation again. Our friends at PowerObjects have written a nice article about this. Another approach would be to run the script below. This shows the most common events (more than 10.000 entries) that are stored in the table. The last column indicates if these records will be removed using the Microsoft KB article.
If you want to filter events based on the amount of occurrences, then you can simply change the threshold.
Enjoy!
declare @threshold as int
set @threshold = 10000
SELECT
Name,
DATEPART(YEAR,CreatedOn) AS [Year],
DATEPART(MONTH,CreatedOn) AS [Month],
case operationtype
when 1 then 'System Event'
when 2 then 'Bulk Email'
when 3 then 'Import File Parse'
when 4 then 'Transform Parse Data'
when 5 then 'Import'
when 6 then 'Activity Propagation'
when 7 then 'Duplicate Detection
Rule Publish'
when 8 then 'Bulk Duplicate
Detection'
when 9 then 'SQM Data Collection'
when 10 then 'Workflow'
when 11 then 'Quick Campaign'
when 12 then 'Matchcode Update'
when 13 then 'Bulk Delete'
when 14 then 'Deletion Service'
when 15 then 'Index Management'
when 16 then 'Collect Organization
Statistics'
when 17 then 'Import Subprocess'
when 18 then 'Calculate
Organization Storage Size'
when 19 then 'Collect Organization
Database Statistics'
when 20 then 'Collection
Organization Size Statistics'
when 21 then 'Database Tuning'
when 22 then 'Calculate
Organization Maximum Storage Size'
when 23 then 'Bulk Delete
Subprocess'
when 24 then 'Update Statistic
Intervals'
when 25 then 'Organization Full
Text Catalog Index'
when 26 then 'Database log backup'
when 27 then 'Update Contract
States'
when 28 then 'DBCC SHRINKDATABASE
maintenance job'
when 29 then 'DBCC SHRINKFILE
maintenance job'
when 30 then 'Reindex all indices
maintenance job'
when 31 then 'Storage Limit
Notification'
when 32 then 'Cleanup inactive
workflow assemblies'
when 35 then 'Recurring Series
Expansion'
when 38 then 'Import Sample Data'
when 40 then 'Goal Roll Up'
when 41 then 'Audit Partition
Creation'
when 42 then 'Check For Language
Pack Updates'
when 43 then 'Provision Language
Pack'
when 44 then 'Update Organization
Database'
when 45 then 'Update Solution'
when 46 then 'Regenerate Entity Row
Count Snapshot Data'
when 47 then 'Regenerate Read Share
Snapshot Data'
when 50 then 'Outgoing Activity'
when 51 then 'Incoming Email
Processing'
when 52 then 'Mailbox Test Access'
when 53 then 'Encryption Health
Check'
when 54 then 'Execute Async
Request'
when 49 then 'Post to Yammer'
when 56 then 'Update Entitlement
States'
else cast(operationtype as varchar)
end as 'OperationType',
case statecode
when 0 then 'Ready'
when 1 then 'Suspended'
when 2 then 'Locked'
when 3 then 'Completed'
else cast (statecode as varchar)
end as 'StateCode',
case statuscode
when 0 then 'Waiting for Resources'
when 10 then 'Waiting'
when 20 then 'In Progress'
when 21 then 'Pausing'
when 22 then 'Canceling'
when 30 then 'Succeeded'
when 31 then 'Failed'
when 32 then 'Canceled'
else cast (statuscode as varchar)
end as 'StatusCode',
Count(*) AS [RecordCount],
case when (OperationType in (1, 9, 12, 25, 27, 10) AND StateCode = 3 AND StatusCode IN (30,32) ) then 'Yes' else 'No' end as 'Removable'
FROM AsyncOperationBase
WHERE StartedOn IS NOT NULL
GROUP BY Name, DATEPART(YEAR,CreatedOn), DATEPART(MONTH,CreatedOn), OperationType, StatusCode, StateCode
having Count(*) > @threshold
order by 2, 3
