AsyncOperationBase Table: Here we go again

It never fails, all is going good and then one of our QA environments (on premise) is having Workflow issues. I needed to look into what was happening on the SQL Server to understand what needed to happen. However, the AsyncOperationBase table is not a easy item to understand. I ended up creating a few SQL scripts to help me understand what was happening. Before we go into the scripts, you must understand more of what the table does and what is in it.

From the SDK

The state of the asynchronous operation can be altered through three means: by the platform as part of regular queue processing, by the user interacting with the Microsoft Dynamics CRM Web application, or by a Web service call that alters the asynchronous operation.

The following table lists the states, and the statuses for each state, of an AsyncOperation.

State

Status

Numeric status value

Ready

WaitingForResources

0

Suspended

Waiting

10

Locked

InProgress

20

Locked

Pausing

21

Locked

Canceling

22

Completed

Succeeded

30

Completed

Failed

31

Completed

Canceled

32

So StateCode and StatusCode flag what is happening, but not to what item in the system. That is where the OperationType field comes into play.

Value

Field

Description

1

Event

Specifies a system event.

2

BulkEmail

Specifies a bulk e-mail operation.

3

Parse

Specifies an import file parse operation.

4

Transform

Specifies an operation to transform parse data.

5

Import

Specifies an import operation.

6

ActivityPropagation

Specifies a campaign activity propagation operation.

7

PublishDuplicateRule

Specifies an operation to publish a duplicate detection rule.

8

BulkDetectDuplicates

Specifies a bulk duplicate detection operation.

9

CollectSqmData

Specifies an operation to collect SQM data.

10

Workflow

Specifies a workflow operation.

11

QuickCampaign

Specifies a quick campaign operation.

12

PersistMatchCode

Specifies an operation to update the matchcode.

13

BulkDelete

Specifies a bulk delete operation.

14

DeletionService

Specifies a Deletion Service operation.

15

IndexManagement

Specifies an index management operation.

16

CollectOrgStats

Specifies an operation to collect an organization’s statistics.

17

ImportingFile

Specifies an import subprocess operation.

18

CalculateOrgStorageSize

Specifies an operation to calculate an organization’s storage size.

19

CollectOrgDBStats

Specifies an operation to collect an organization’s database statistics.

20

CollectOrgSizeStats

Specifies an operation to collection an organization’s size statistics.

21

DatabaseTuning

Specifies an operation for database tuning.

22

CalculateOrgMaxStorageSize

Specifies an operation to calculate an organization’s maximum storage size.

23

BulkDeleteChild

Specifies a bulk delete subprocess operation.

24

UpdateStatisticIntervals

Specifies an operation to update statistic intervals.

25

FullTextCatalogIndex

Specifies an operation to generate an organization full text catalog index.

26

DatabaseLogBackup

 

27

UpdateContractStates

Specifies an operation to update contract states.

28

ShrinkDatabase

 

29

ShrinkLogFile

 

30

ReindexAll

Specifies an index management operation.

31

StorageLimitNotification

 

32

CleanupInactiveWorkflowAssemblies

 

38

ImportSampleData

Bottom of Form

These tables helped me understand better how the table is ordered and processed. Each OperationType will have the different statuses mentioned above.

So the SQL Statement to help me see what was happening was the following

SELECT     
	operationtypename, 
	statecodename, 
	statuscodename, 
	COUNT(asyncoperationid) AS cnt
FROM         
	FilteredAsyncOperation
GROUP BY 
	statuscodename, 
	statecodename, 
	operationtypename
ORDER BY 
	operationtypename, 
	statecodename, 
	statuscodename

Which gave me the following results.

Bulk Delete

Completed

Failed

1

Bulk Delete

Completed

Succeeded

23

Bulk Delete

Locked

Canceling

1

Bulk Delete

Ready

Waiting For Resources

1

Bulk Delete

Suspended

Waiting

8

Bulk Delete Subprocess

Completed

Succeeded

18

Bulk E-mail

Completed

Succeeded

1

Goal Roll Up

Completed

Failed

1

Goal Roll Up

Locked

Canceling

1

Import

Completed

Succeeded

3

Import File Parse

Completed

Succeeded

3

Import Subprocess

Completed

Succeeded

6

Recurring Series Expansion

Suspended

Waiting

1

System Event

Completed

Failed

590

System Event

Locked

Canceling

1513

System Event

Ready

Waiting For Resources

10717

Transform Parse Data

Completed

Succeeded

3

Workflow

Completed

Failed

4

Workflow

Locked

Canceling

484

Workflow

Ready

Waiting For Resources

453

Advertisements
This entry was posted in Onpremise, performance, SQL, SQL 2005, SQL 2008, SQL 2012, SQL Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s