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.


declare @threshold as int

set @threshold = 10000



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


GROUP BY Name, DATEPART(YEAR,CreatedOn), DATEPART(MONTH,CreatedOn), OperationType, StatusCode, StateCode

having Count(*) > @threshold

order by 2, 3


UniversalTelecoms said...

Very Nice Article to read. Blog articles helped me alot in many ways to find the good solution. Am very happy to comments here. Thanks for sharing this kind of wonderful article in this blog. Lovely !
CCTV Camera Supplier

Packers And Movers In Delhi said...

Get Shifting/Relocation Quotation from ###Packers and Movers Delhi. Packers and Movers Delhi 100% Affordable and Reliable ***Household Shifting Services. Compare Transportation Charges and Save Time, Verified and Trusted Packers and Movers in Delhi, Cheap and Safe Local, Domestic House Shifting @
Packers and Movers Delhi

Packers And Movers Mumbai said...

Hire Best Packers And Movers Mumbai for hassle-free Household Shifting, ***Office Relocation, ###Car Transporation, Loading Unloading, packing Unpacking at affordable ✔✔✔ Price Quotation. Top Rated, Safe and Secure Service Providers who can help you with 24x7 and make sure a Untroubled Relocation Services at Cheapest/Lowest Rate
Packers And Movers Mumbai

Packers and Movers Bangalore said...

Packers And Movers Bangalore Local Household Shifting Service, Get Free Best Price Quotes Local Packers and Movers in Bangalore List , Compare Charges, Save Money And Time @ Packers And Movers in Bangalore

Packers and Movers Chennai said...

Packers and Movers Chennai Give Safe and Reliable ***Household Shifting Services in Chennai with Reasonable ###Packers and Movers Price Quotation. We Provide Household Shifting, Office Relocation, ✔✔✔Local and Domestic Transportation Services, Affordable and Reliable Shifting Service Charges @
Packers And Movers Chennai

Packers and Movers Bangalore said...

Packers and Movers Bangalore as a Services providing company can make all the difference to your ###Home Relocation experience. Bangalore based Company which offers versatile solutions, Right team that easily reduce the stress associated with a ✔✔✔Household Shifting, ***Vehicle Transportation. we help things run smoothly and reduce breakages and offer you seamless, Affordable, Reliable Shifting Services, Compare Shifting Charges @
Packers And Movers Bangalore

Packers and Movers Jaipur said...

Get Packers and Movers Jaipur List of Top Reliable, 100% Affordable, Verified and Secured Service Provider. Get Free ###Packers and Movers Jaipur Price Quotation instantly and Save Cost and Time. Packers and Movers Jaipur ✔✔✔Reviews and Compare Charges for household Shifting, Home/Office Relocation, ***Car Transportation, Pet Relocation, Bike SHifting @
Packers And Movers Jaipur

Packers and Movers Gurgaon said...

Packers and Movers Gurgaon Provide Reliable, Safe and Certified Service Provider list, Get Free ***Best Price Quotaition and Compare Charges. ✔✔✔ Hassle free Household Shifting Services, High Quality packing Material, Office Relocation, Car Transportaion, ###Local and Domestic Shifting Service @
Packers And Movers Gurgaon

Packers And Movers Hyderabad said...

Packers and Movers Hyderabad Give Certified and Verified Service Providers, Cheap and Best ###Office Relocation Charges, ***Home Shifting, ✔ ✔ ✔ Goods Insurance worth Rs. 10,000, Assurance for Local and Domestic House Shifting. Safe and Reliable Household Shifting Services in Hyderabad with Reasonable Packers and Movers Price Quotation @
Packers And Movers Hyderabad

steve said...

تنظيف المنازل يحتاج الى عمال محترفين الرائد افضل شركة تنظيف منازل بجدة تقدم افضل خدمات التنظيف للمنازل و الشقق و السجاد و الموكيت وجميع مفروشات المنازل فنحن افضل شركة تنظيف بجدة اتصل بنا الان و قم بزيارة ايضا :
شركة تنظيف منازل بالدمام

Packers And Movers Mumbai said...

Packers And Movers Mumbai to Haridwar
Packers And Movers Mumbai to Noida
Packers And Movers Mumbai to Guntur
Packers And Movers Mumbai to Muzaffarpur

Packers and Movers in Delhi said...

Packers and Movers in Delhi - Air payload Packers and Movers may be a Ncr primarily based Packers Movers Company providing reliable and efficient Packers Movers services, Packers and Movers in Ncr and Packing Moving services all told over Asian nation. it's branches all told the most cities of Asian nation..

Packers and Movers Pune said...

Packers And Movers Pune to Chennai
Packers And Movers Pune to Hyderabad
Packers And Movers Pune to Bhubaneswar
Packers And Movers Pune to Bhopal
Packers And Movers Pune to Lucknow

SUNITA said...

Book Best and Verified packers and movers in Pune, Expert in Home Relocation within or outside Pune. Compare 3 quotes from nearby packers and movers in Pune. Get 100% safe & affordable packing moving from Professional Packers and Movers.

Packers and Movers Pune

packers and movers Gurgaon said...

Hiring packers and movers in Gurgaon is not too hard if you take help of the best moving professionals. Best movers and packers Gurgaon assist with their skilled and trained team to pack, load, unload and unpack your belongings.
So be aware and hire the best mover packer to save your valuables.
packers and movers Gurgaon
packers and movers Gurgaon Charges

SUNITA said...

Book Best and Verified packers and movers in Gurgaon and Pune, Expert in Home Relocation within or outside Gurgaon and Pune. Compare 3 quotes from nearby packers and movers in Pune and Gurgaon. Get 100% safe & affordable packing moving from Professional Packers and Movers.

Packers and movers in Gurgaon
Packers and movers rates in Gurgaon
Packers and movers Gurgaon charges
Packers and movers rates in Pune
Packers and movers Pune charges
Packers and movers Pune rates