Export (0) Print
Expand All

core.sp_create_snapshot (Transact-SQL)

Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

Inserts a row in the management data warehouse core.snapshots view. This procedure is called every time an upload package starts uploading data to the management data warehouse.

Applies to: SQL Server (SQL Server 2008 through current version).

Topic link icon Transact-SQL Syntax Conventions

core.sp_create_snapshot [ @collection_set_uid = ] 'collection_set_uid'
    , [ @collector_type_uid = ] 'collector_type_uid'
    ,[ @machine_name = ] 'machine_name'
    , [ @named_instance = ] 'named_instance'
    , [ @log_id = ] log_id 
    , [ @snapshot_id = ] snapshot_id OUTPUT

[ @collection_set_uid = ] 'collection_set_uid'

The GUID for the collection set. collection_set_uid is uniqueidentifier with no default value. To obtain the GUID, query the dbo.syscollector_collection_sets view in the msdb database.

[ @collector_type_uid = ] 'collector_type_uid'

The GUID for a collector type. collector_type_uid is uniqueidentifier with no default value. To obtain the GUID, query the dbo.syscollector_collector_types view in the msdb database.

[ @machine_name= ] 'machine_name'

The name of the server that the collection set resides on. machine_name is sysname, with no default value.

[ @named_instance= ] 'named_instance'

The name of the instance for the collection set. named_instance is sysname, with no default value.

[ @log_id = ] log_id

The unique identifier that maps to the collection set event log on the server that collected the data. log_id is bigint with no default value. To obtain the value for log_id, query the dbo.syscollector_execution_log view in the msdb database.

[ @snapshot_id = ] snapshot_id

The unique identifier for a row that is inserted into the core.snapshots view. snapshot_id is int and is returned as OUTPUT.

0 (success) or 1 (failure)

Every time an upload package starts uploading data to the management data warehouse, the data collector run-time component calls core.sp_create_snapshot.

This procedure checks to see if:

  • The collection_set_uid matches an existing entry in the core.source_info_internal table.

  • The collector_type_uid matches an existing entry in the core.supported_collector_types view.

If either of the preceding checks fails, the procedure fails and returns an error.

Requires membership in the mdw_writer (with EXECUTE permission) fixed database role.

The following example creates a snapshot for the Disk Usage collection set, adds it to the management data warehouse, and returns the snapshot identifier. In the example, the default instance is used.

USE <management_data_warehouse>;
DECLARE @snapshot_id int;
EXEC core.sp_create_snapshot 
    @collection_set_uid = '7B191952-8ECF-4E12-AEB2-EF646EF79FEF', 
    @collector_type_uid = '302E93D1-3424-4BE7-AA8E-84813ECF2419',
    @machine_name = '<computername>',
    @named_instance = 'MSSQLSERVER',
    @log_id = 11, -- ID of the log for the collection set
    @snapshot_id = @snapshot_id OUTPUT;
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2015 Microsoft