Resolution | From the Job Engine Manager, it's not possible to get more details about Calculation Jobs. The named_arguments column contains details like Level Id, a list of calculated Field Ids, and a list of Content Ids. The information is hard to get since the named_arguments type is XML, but is helpful for troubleshooting or before killing the job.
Run the following SQL query against the Instance database to get details on running calculation jobs.
;WITH XMLNAMESPACES('http://schemas.microsoft.com/2003/10/Serialization/Arrays' AS a ,'http://www.w3.org/2001/XMLSchema-instance' AS i ,'http://www.w3.org/2001/XMLSchema' AS x ,'http://schemas.microsoft.com/2003/10/Serialization/' AS z ,'http://schemas.datacontract.org/2004/07/System.Collections.Generic' AS g ,'http://schemas.microsoft.com/2003/10/Serialization/Arrays' AS d7p1 )
SELECT j.JobId, j.JobType, j.Endpoint AS Server, j.process_id AS ProcessId, j.JobOrder , ai.StartTime, ai.EnqueueTime , j.Priority, j.EnqueuedDate, j.Generation, j.ParentJobId, j.root_job_id AS RootJobId, j.job_num_id AS JobNumId , mt.module_name AS ModuleName, lt.level_name AS LevelName , j.named_arguments.value('(/a:ArrayOfKeyValueOfstringanyType[1]/KeyValuePairs[1]/g:KeyValuePairOfstringanyType[1]/g:key[1])', 'nvarchar(max)') AS KeyName , j.named_arguments.value('(/a:ArrayOfKeyValueOfstringanyType[1]/KeyValuePairs[1]/g:KeyValuePairOfstringanyType[1]/g:value[1])', 'nvarchar(max)') AS KeyId , j.named_arguments.value('(/a:ArrayOfKeyValueOfstringanyType[1]/KeyValuePairs[1]/g:KeyValuePairOfstringanyType[2]/g:value[1]/KeyValuePairs[1]/*[1]/*[1]/a:_size[1])', 'nvarchar(max)') AS CalcFieldCount , j.named_arguments.value('(/a:ArrayOfKeyValueOfstringanyType[1]/KeyValuePairs[1]/g:KeyValuePairOfstringanyType[2]/g:value[1]/KeyValuePairs[1]/*[1]/*[2]/a:_size[1])', 'nvarchar(max)') AS ContentCount , j.named_arguments.query('for $v in data(/a:ArrayOfKeyValueOfstringanyType[1]/KeyValuePairs[1]/g:KeyValuePairOfstringanyType[2]/g:value[1]/KeyValuePairs[1]/*[1]/*[1]/a:_items[1]/*) return if ($v!=0) then $v else ""').value('.', 'nvarchar(max)') AS CalcFields , j.named_arguments.query('for $v in data(/a:ArrayOfKeyValueOfstringanyType[1]/KeyValuePairs[1]/g:KeyValuePairOfstringanyType[2]/g:value[1]/KeyValuePairs[1]/*[1]/*[2]/a:_items[1]/*) return if ($v!=0) then $v else ""').value('.', 'nvarchar(max)') AS ContentIds
FROM tblAsyncJobQueue j WITH (NOLOCK) LEFT OUTER JOIN tblAsyncInstrumentation ai WITH (NOLOCK) ON ai.JobId = j.JobId LEFT OUTER JOIN tblLevel l WITH (NOLOCK) ON l.level_id = j.named_arguments.value('(/a:ArrayOfKeyValueOfstringanyType[1]/KeyValuePairs[1]/g:KeyValuePairOfstringanyType[1]/g:value[1])', 'nvarchar(max)') LEFT OUTER JOIN tblLevelTranslation lt WITH (NOLOCK) ON lt.level_id = l.level_id LEFT OUTER JOIN tblModuleTranslation mt WITH (NOLOCK) ON mt.module_id = l.module_id WHERE j.JobType LIKE '%calculation%' AND Endpoint IS NOT NULL ORDER BY ai.StartTime
|