- syscollector_collection_items
- syscollector_collection_sets
- syscollector_collector_types
- syscollector_config_store
- syscollector_execution_log
- syscollector_execution_log_full
- syscollector_execution_stats
syscollector_collection_items
Returns information about an item in a collection set.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
collection_set_id | • | • | • | int | Identifies the collection set. Is not nullable. | |
collection_item_id | • | • | • | int | Identifies an item in the collection set. Is not nullable. | |
collector_type_uid | • | • | • | uniqueidentifier | The GUID used to identify the collector type. Is not nullable. | |
name | • | • | • | nvarchar(4000) | The name of the collection set. Is nullable. | |
frequency | • | • | • | int | The frequency that data is collected by a collection item. Is not nullable. | |
parameters | • | • | • | xml | Describes the parameterization for the collector type associated with the collection item. The XML schema for this collection item is validated with the XML Schema (XSD) stored in the parameter_schema for a particular collector type. Is nullable. For more information, see syscollector_collector_types (Transact-SQL)1. |
TSQL
Sql 2008SELECT [collection_set_id], [collection_item_id], [collector_type_uid], [name], [frequency], [parameters] FROM syscollector_collection_items
Sql 2008 R2
SELECT [collection_set_id], [collection_item_id], [collector_type_uid], [name], [frequency], [parameters] FROM syscollector_collection_items
Sql 2012
SELECT [collection_set_id], [collection_item_id], [collector_type_uid], [name], [frequency], [parameters] FROM syscollector_collection_items
Back to Top
syscollector_collection_sets
Provides information about a collection set, including schedule, collection mode, and its state.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
collection_set_id | • | • | • | int | The local identifier for the collection set. Is not nullable. | |
collection_set_uid | • | • | • | uniqueidentifier | The globally unique identifier for the collection set. Is not nullable. | |
name | • | • | • | nvarchar(4000) | The name of the collection set. Is nullable. | |
target | • | • | • | nvarchar(max) | Identifies the target for the collection set. Is nullable. | |
is_system | • | • | • | bit | Turned on (1) or off (0) to indicate if the collection set was included with the data collector or if it was added later by the dc_admin. This could be a custom collection set developed in-house or by a third party. Is not nullable. | |
is_running | • | • | • | bit | Indicates whether or not the collection set is running. Is not nullable. | |
collection_mode | • | • | • | smallint | Specifies the collection mode for the collection set. Is not nullable. Collection mode is one of the following: 0 - Cached mode. Data collection and upload are on separate schedules. 1 - Non-cached mode. Data collection and upload are on the same schedule. |
|
proxy_id | • | • | • | int | Identifies the proxy that is used to run the collection set job step. Is nullable. | |
schedule_uid | • | • | • | uniqueidentifier | Provides a pointer to the collection set schedule. Is nullable. | |
collection_job_id | • | • | • | uniqueidentifier | Identifies the collection job. Is nullable. | |
upload_job_id | • | • | • | uniqueidentifier | Identifies the collection upload job. Is nullable. | |
logging_level | • | • | • | smallint | Specifies the logging level (0, 1 or 2). Is not nullable. For more information about logging levels, see Data Collector Logging1. | |
days_until_expiration | • | • | • | smallint | The number of days that the collected data is saved in the management data warehouse. Is not nullable. | |
description | • | • | • | nvarchar(4000) | Describes the collection set. Is nullable. | |
dump_on_any_error | • | • | • | bit | Turned on (1) or off (0) to indicate whether to create an SSIS dump file on any error. Is not nullable. | |
dump_on_codes | • | • | • | nvarchar(max) | Contains the list of SSIS error codes that are used to trigger the dump file. Is nullable. For more information about how to obtain a dump file, see the How to enable the Sqldumper.exe utility to generate dump files for processes that are related to SQL Server 2005 Integration Services with Service Pack 22 article in the Microsoft Knowledge Base. |
TSQL
Sql 2008SELECT [collection_set_id], [collection_set_uid], [name], [target], [is_system], [is_running], [collection_mode], [proxy_id], [schedule_uid], [collection_job_id], [upload_job_id], [logging_level], [days_until_expiration], [description], [dump_on_any_error], [dump_on_codes] FROM syscollector_collection_sets
Sql 2008 R2
SELECT [collection_set_id], [collection_set_uid], [name], [target], [is_system], [is_running], [collection_mode], [proxy_id], [schedule_uid], [collection_job_id], [upload_job_id], [logging_level], [days_until_expiration], [description], [dump_on_any_error], [dump_on_codes] FROM syscollector_collection_sets
Sql 2012
SELECT [collection_set_id], [collection_set_uid], [name], [target], [is_system], [is_running], [collection_mode], [proxy_id], [schedule_uid], [collection_job_id], [upload_job_id], [logging_level], [days_until_expiration], [description], [dump_on_any_error], [dump_on_codes] FROM syscollector_collection_sets
Back to Top
syscollector_collector_types
Provides information about a collector type for a collection item.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
collector_type_uid | • | • | • | uniqueidentifer | The GUID for a collection type. Is not nullable. | |
name | • | • | • | sysname | The name of the collection type. Is not nullable. | |
parameter_schema | • | • | • | xml | The XML schema that describes what the configuration for the specified collector type looks like. This XML schema is used to validate the actual XML configuration associated with a particular collection item instance. Is nullable. | |
parameter_formatter | • | • | • | xml | Determines the template to use to transform the XML for use in the collection set property page. Is nullable. | |
collection_package_id | • | • | • | uniqueidentifer | The GUID for a collection package. Is not nullable. | |
collection_package_path | • | • | • | nvarchar(4000) | Provides the path to the collection package. Is nullable. | |
collection_package_name | • | • | • | sysname | The name of the collection package. Is not nullable. | |
upload_package_id | • | • | • | uniqueidentifer | The GUID for the upload package. Is not nullable. | |
upload_package_path | • | • | • | nvarchar(4000) | Provides the path to the upload package. Is nullable. | |
upload_package_name | • | • | • | sysname | The name of the upload package. Is not nullable. | |
is_system | • | • | • | bit | Turned on (1) or off (0) to indicate if the collector type was shipped with the data collector or if it was added later by the dc_admin. This could be a custom type developed in-house or by a third party. Is not nullable. |
TSQL
Sql 2008SELECT [collector_type_uid], [name], [parameter_schema], [parameter_formatter], [collection_package_id], [collection_package_path], [collection_package_name], [upload_package_id], [upload_package_path], [upload_package_name], [is_system] FROM syscollector_collector_types
Sql 2008 R2
SELECT [collector_type_uid], [name], [parameter_schema], [parameter_formatter], [collection_package_id], [collection_package_path], [collection_package_name], [upload_package_id], [upload_package_path], [upload_package_name], [is_system] FROM syscollector_collector_types
Sql 2012
SELECT [collector_type_uid], [name], [parameter_schema], [parameter_formatter], [collection_package_id], [collection_package_path], [collection_package_name], [upload_package_id], [upload_package_path], [upload_package_name], [is_system] FROM syscollector_collector_types
Back to Top
syscollector_config_store
Returns properties that apply to the entire data collector, as opposed to a collection set instance. Each row in this view describes a specific data collector property, such as the name of the management data warehouse, and the instance name where the management data warehouse is located.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
parameter_name | • | • | • | nvarchar(128) | The name of the property. Is not nullable. | |
parameter_value | • | • | • | sql_variant | The actual value of the property. Is nullable. |
TSQL
Sql 2008SELECT [parameter_name], [parameter_value] FROM syscollector_config_store
Sql 2008 R2
SELECT [parameter_name], [parameter_value] FROM syscollector_config_store
Sql 2012
SELECT [parameter_name], [parameter_value] FROM syscollector_config_store
Back to Top
syscollector_execution_log
Provides information from the execution log for a collection set or package.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
log_id | • | • | • | bigint | Identifies each collection set execution. Used to join this view with other detailed logs. Is not nullable. | |
parent_log_id | • | • | • | bigint | Identifies the parent package or collection set. Is not nullable. The IDs are chained in the parent-child relationship, which enables you to determine which package was started by which collection set. This view groups the log entries by their parent-child linkage and indents the names of the packages, so that the call chain is clearly visible. | |
collection_set_id | • | • | • | int | Identifies the collection set or package that this log entry represents. Is not nullable. | |
collection_item_id | • | • | • | int | Identifies a collection item. Is nullable. | |
start_time | • | • | • | datetime | The time that the collection set or package started. Is not nullable. | |
last_iteration_time | • | • | • | datetime | For continuously running packages, the last time that the package captured a snapshot. Is nullable. | |
finish_time | • | • | • | datetime | The time the run completed for finished packages and collection sets. Is nullable. | |
runtime_execution_mode | • | • | • | smallint | Indicates whether the collection set activity was collecting data or uploading data. Is nullable. Values are: 0 = Collection 1 = Upload |
|
status | • | • | • | smallint | Indicates the current status of the collection set or package. Is not nullable. Values are: 0 = running 1 = finished 2 = failed |
|
operator | • | • | • | nvarchar(128) | Identifies who started the collection set or package. Is not nullable. | |
package_id | • | • | • | uniqueidentifier | Identifies the collection set or package that generated this log. Is nullable. | |
package_name | • | • | • | nvarchar(4000) | The name of the package that generated this log. Is nullable. | |
package_execution_id | • | • | • | uniqueidentifier | Provides a link to the SSIS log table. Is nullable. | |
failure_message | • | • | • | nvarchar(2048) | If the collection set or package failed, the most recent error message for that component. Is nullable. To obtain more detailed error information, use the fn_syscollector_get_execution_details (Transact-SQL)1 function. |
TSQL
Sql 2008SELECT [log_id], [parent_log_id], [collection_set_id], [collection_item_id], [start_time], [last_iteration_time], [finish_time], [runtime_execution_mode], [status], [operator], [package_id], [package_name], [package_execution_id], [failure_message] FROM syscollector_execution_log
Sql 2008 R2
SELECT [log_id], [parent_log_id], [collection_set_id], [collection_item_id], [start_time], [last_iteration_time], [finish_time], [runtime_execution_mode], [status], [operator], [package_id], [package_name], [package_execution_id], [failure_message] FROM syscollector_execution_log
Sql 2012
SELECT [log_id], [parent_log_id], [collection_set_id], [collection_item_id], [start_time], [last_iteration_time], [finish_time], [runtime_execution_mode], [status], [operator], [package_id], [package_name], [package_execution_id], [failure_message] FROM syscollector_execution_log
Back to Top
syscollector_execution_log_full
Provides information about a collection set or package when the execution log is full.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
log_id | • | • | • | bigint | Identifies each collection set execution. Used to join this view with other detailed logs. Is nullable. | |
parent_log_id | • | • | • | bigint | Identifies the parent package or collection set. Is not nullable. The IDs are chained in the parent-child relationship, which enables you to determine which package was started by which collection set. This view groups the log entries by their parent-child linkage and indents the names of the packages so that the call chain is clearly visible. | |
name | • | • | • | nvarchar(4000) | The name of the collection set or package that this log entry represents. Is nullable. | |
status | • | • | • | smallint | Indicates the current status of the collection set or package. Is nullable. Values are: 0 = running 1 = finished 2 = failed |
|
runtime_execution_mode | • | • | • | smallint | Indicates whether the collection set activity was collecting data or uploading data. Is nullable. | |
start_time | • | • | • | datetime | The time that the collection set or package started. Is nullable. | |
last_iteration_time | • | • | • | datetime | For continuously running packages, the last time that the package captured a snapshot. Is nullable. | |
finish_time | • | • | • | datetime | The time the run completed for finished packages and collection sets. Is nullable. | |
duration | • | • | • | int | The time, in seconds, that the package or collection set has been running. Is nullable. | |
failure_message | • | • | • | nvarchar(2048) | If the collection set or package failed, the most recent error message for that component. Is nullable. To obtain more detailed error information, use the fn_syscollector_get_execution_details (Transact-SQL)1 function. | |
operator | • | • | • | nvarchar(128) | Identifies who started the collection set or package. Is nullable. | |
package_execution_id | • | • | • | uniqueidentifier | Provides a link to the SSIS log table. Is nullable. | |
collection_set_id | • | • | • | int | Provides a link to the data collection configuration table in msdb. Is nullable. |
TSQL
Sql 2008SELECT [log_id], [parent_log_id], [name], [status], [runtime_execution_mode], [start_time], [last_iteration_time], [finish_time], [duration], [failure_message], [operator], [package_execution_id], [collection_set_id] FROM syscollector_execution_log_full
Sql 2008 R2
SELECT [log_id], [parent_log_id], [name], [status], [runtime_execution_mode], [start_time], [last_iteration_time], [finish_time], [duration], [failure_message], [operator], [package_execution_id], [collection_set_id] FROM syscollector_execution_log_full
Sql 2012
SELECT [log_id], [parent_log_id], [name], [status], [runtime_execution_mode], [start_time], [last_iteration_time], [finish_time], [duration], [failure_message], [operator], [package_execution_id], [collection_set_id] FROM syscollector_execution_log_full
Back to Top
syscollector_execution_stats
Provides information about task execution for a collection set or package.
Column name | Sql 2005 | Sql 2008 | Sql 2008 R2 | Sql 2012 | Type | Description |
---|---|---|---|---|---|---|
log_id | • | • | • | bigint | Identifies each collection set execution. Used to join this view with other detailed logs. Is not nullable. | |
task_name | • | • | • | nvarchar(128) | The name of the collection set or package task that this information is for. Is not nullable. | |
execution_row_count_in | • | • | • | int | Number of rows processed at the beginning of data flow. Is nullable. | |
execution_row_count_out | • | • | • | int | Number of rows processed at the end of data flow. Is nullable. | |
execution_row_count_errors | • | • | • | int | Number of rows that failed during the data flow. Is nullable. | |
execution_time_ms | • | • | • | int | The time, in milliseconds, required for the task to complete. Is nullable. | |
log_time | • | • | • | datetime | The time that this information was logged. Is not nullable. |
TSQL
Sql 2008SELECT [log_id], [task_name], [execution_row_count_in], [execution_row_count_out], [execution_row_count_errors], [execution_time_ms], [log_time] FROM syscollector_execution_stats
Sql 2008 R2
SELECT [log_id], [task_name], [execution_row_count_in], [execution_row_count_out], [execution_row_count_errors], [execution_time_ms], [log_time] FROM syscollector_execution_stats
Sql 2012
SELECT [log_id], [task_name], [execution_row_count_in], [execution_row_count_out], [execution_row_count_errors], [execution_time_ms], [log_time] FROM syscollector_execution_stats
Back to Top
No comments:
Post a Comment