SQL Query for System JOB CRM

Hello Friend, this article will explain about the system jobs ( Workflows, Dialogs, Imports etc.) in a details view using SQL query.

The asyncoperation table stores details of system jobs (including asyn plugins). The following table details some of the different job types that are stored in the table.

The below query can be used to analysis system jobs of CRM in SQL server.

SELECT asyncoperationid — Primary Key of the async job record
,name — Name of the async job
,statecodename — State of the async job record
,statuscodename — Status of the async job record
,createdbyname — Created by user name
,createdon — Created on (local date time) of workflow  
,createdonutc — Created on (UTC date time) of workflow
,modifiedbyname — Modified by User name
,modifiedon — Modified on date time (Local)
,modifiedonutc — Modified on date time (UTC)
,startedon — The datetime the job started (Local)
,startedonutc — The datetime the job started (UTC)
,completedon — The datetime the job completed (Local)
,completedonutc — The datetime the job started (UTC)
,depth — This is for async plugins defines the depth of the async plugin
,errorcode — Error code of the failed job
,executiontimespan — The time taken for the execution
,friendlymessage — Message of the failed job
,message — Details message of the failed job
,messagename — Event Name
,operationtypename — Operation Type
,primaryentity = (select top 1 name from entity where objecttypecode= primaryentitytype) — Entity Name
,regardingobjectidname — Regarding object
,owneridname — Owner of the job
,regardingobjectentity = (select top 1 name from entity where objecttypecode= regardingobjecttypecode) —Regarding entity
FROM FilteredAsyncOperation

The below table defines the operation types and status code

For removing workflow expansion tasks

http://support.microsoft.com/kb/957871/

For removing completed workflow entries
http://support.microsoft.com/kb/968755/