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