sprockit.Execution

[ v1.3 ] [ v1.4 ] [ v1.5 ] [ Back to Sprockit home page ]

A row in this table records a single execution of a process. You can add information to a process's execution record using the utility SP sprockit.usp__LogExecutionProperty.

Records in the table that are older (in days) than Sprockit's LogRetentionPeriod property are automatically deleted. If you are logging custom properties in this table or using execution IDs to track process lineage, you should disable this behaviour by setting LogRetentionPeriod to -1.

Column name1) Column type Description Notes
PK ExecutionId INT Unique identifier for the execution attempt. IDENTITY
RL1 ProcessId INT Identifies the process executed.
RL2 HandlerId INT Identifies the handler which executed the process.
StartDateTime DATETIME The date and time at which the handler indicated by [HandlerId] reserved the process for execution.
EndDateTime DATETIME The date and time at which execution of the process stopped.
EndStatus VARCHAR(20) The status with which execution stopped. Usually Done, occasionally Errored.
ErrorNumber INT The SQL Server error number of the error which caused execution to stop. Only present when [EndStatus] = Errored. Failed SSIS package executions always have [ErrorNumber] = 50000. This is because Sprockit reads package failures from SSISDB execution logs and raises them internally as generic SQL errors.
Execution Configuration XML Additional information about the process's runtime configuration. Values in this field can be shredded using the function sprockit.tvf_ParsePropertyValue. Since Sprockit v1.4. For SSIS packages, a Property node with name SsisExecutionId indicates the SSIS catalog's internal execution ID for the package (refers to the SSISDB view [catalog].[executions]). Values logged using the utility SP sprockit.usp__LogExecutionProperty are added to this field as new Property nodes.
ProcessType VARCHAR(4) Copied from sprockit.uvw_Process at [StartDateTime].
IsEnabled BIT Copied from sprockit.uvw_Process at [StartDateTime].
Priority TINYINT Since Sprockit v1.4. Copied from sprockit.uvw_Process at [StartDateTime].
AvgDuration INT Copied from sprockit.uvw_Process at [StartDateTime].
BranchWeight INT Copied from sprockit.uvw_Process at [StartDateTime].
LogLevel TINYINT Since Sprockit v1.5. Copied from sprockit.uvw_Process at [StartDateTime].

Relationships with integrity enforced by a foreign key constraint have identifiers prefixed with FK; others are prefixed RL.

Click here to view the data model diagram.

Attributes
(in this table)
Referenced table Referenced attributes
(in referenced table)
RL2 HandlerId sprockit.Handler HandlerId
RL1 ProcessId sprockit.Process ProcessId
Relationship Attributes
(in referencing table)
Referenced attributes
(in this table)
sprockit.Error.RL1 ExecutionId ExecutionId
sprockit.Event.RL1 ExecutionId ExecutionId
sprockit.IndexMaintenanceTask.RL1 ExecutionId ExecutionId
sprockit.Process.RL1 LatestExecutionId ExecutionId
sprockit.SsisPerformanceCounters.RL1 ExecutionId ExecutionId
sprockit.uvw_Execution.RL1 ExecutionId ExecutionId

Execution configurations recorded in the [ExecutionConfiguration] field can be parsed using the function sprockit.tvf_ParsePropertyValue. For example, to extract SSIS execution IDs (for reference to log entries in the integration services catalog) you can use:

SELECT 
  e.ExecutionId AS SprockitExecutionId
, e.[ExecutionConfiguration]
, CAST(tvf.PropertyValue AS INT) AS SsisExecutionId
FROM [sprockit].[Execution] e
  OUTER APPLY sprockit.tvf_ParsePropertyValue('SsisExecutionId', e.[ExecutionConfiguration]) tvf

Page generated from database metadata by DbScout.


1)
Columns with names in bold are non-nullable; columns with underlined names are participants in the table's primary key.