Setting Up Your Custom Data Collector Set


From my previous post, we learned how to set up Data Collection in SQL Server. This is most suitable for monitoring purposes and reporting on disk usage and server activities. But what if you want to monitor other areas of your database server aside from the default data collection sets?


Aside from the default data collection sets, you can also set up your own customised data collection sets. For example your requirement would be to monitor all failed SQL Agent jobs on a daily basis across all your SQL database servers. Lets use this as an example.

In creating a customised data collection set, the step is composed of 3 parts;

1. defining the data collection container – which contains header parameters such as name of the data collection set, description, logging and schedule to run.  You will need to use the stored procedure sp_syscollector_create_collection_set. See example below;

EXEC [msdb].[dbo].[sp_syscollector_create_collection_set]
                                    @name=N’Failed SQL Jobs’,
                                    @description=N’Collects data about failed jobs for all servers.’,
                                    @collection_set_id=@collection_set_id_1 OUTPUT,
                                    @collection_set_uid=@collection_set_uid_2 OUTPUT

2. defining the data collector type – this is the part you will define the data collector type for your custom data collection. There are predefined data collector type already setup for use. For most usual cases we will use the Generic T-SQL Query Collector Type.   To retrieve this value you may use the statement below;

Declare @collector_type_uid_3 uniqueidentifier
                  Select @collector_type_uid_3 = collector_type_uid
                  From [msdb].[dbo].[syscollector_collector_types]
                  Where name = N’Generic T-SQL Query Collector Type’;

3. Define the data collection item – this is the part wherein you define your parameters for your data collection set.  This will also contain the actual query to retrieve all SQL failed jobs. As an example see the code below

Declare @collection_item_id_4 int
EXEC [msdb].[dbo].[sp_syscollector_create_collection_item]
                                    @name=N’Failed SQL Jobs Item’,
                                    @parameters=N'<ns:TSQLQueryCollector xmlns:ns=”DataCollectorType”><Query><Value>
                                    SELECT  @@ServerName AS [ServerName],
                                                      [sJOB].[name] AS [JobName],
                                                      [sJOBH].[run_date]  AS [LastRunDateTime],
                                                      CAST([sJOBH].[run_duration] AS VARCHAR(6)) AS [LastRunDuration (HH:MM:SS)],
                                                      [sJOBH].[message] AS [LastRunStatusMessage],
                                                      CAST([sJOBSCH].[NextRunDate] AS CHAR(8)),
                                                      [sJOBSCH].[NextRunDate] AS [NextRunDateTime]
                                    FROM [msdb].[dbo].[sysjobs] AS [sJOB] LEFT JOIN (SELECT [job_id], MIN([next_run_date]) AS [NextRunDate]
                    , MIN([next_run_time]) AS [NextRunTime]
                FROM [msdb].[dbo].[sysjobschedules]
                GROUP BY [job_id] ) AS [sJOBSCH] ON [sJOB].[job_id] = [sJOBSCH].[job_id]
                 LEFT JOIN (SELECT [job_id] , [run_date] , [run_time]          , [run_status], [run_duration], [message], ROW_NUMBER() OVER (PARTITION BY [job_id] ORDER BY [run_date] DESC, [run_time] DESC) AS RowNumber FROM [msdb].[dbo].[sysjobhistory] WHERE [step_id] = 0 ) AS [sJOBH] ON [sJOB].[job_id] = [sJOBH].[job_id] AND [sJOBH].[RowNumber] = 1    WHERE  [sJOBH].[run_status] = ”0”                                    ORDER BY [LastRunDateTime] DESC

                        </Value><OutputTable> FailedJobs</OutputTable> </Query><Databases UseSystemDatabases=”true” UseUserDatabases=”true” /> </ns:TSQLQueryCollector>’, 
                                    @collection_item_id=@collection_item_id_4 OUTPUT,

The <OutputTable> tag contains the destination table for your query results. This table will be created with default schema of custom_snapshots inside your Management Data Warehouse database.

After successfully creating your custom data collection set, you must manually start the data collection. You can either do this via GUI from the Data Collection menu or by executing below under the msdb database.

EXEC sp_syscollector_start_collection_set @collection_set_id = <yourcollectionsetid>;

After the initial upload of data thats the time the output table for your data collection set will be created. Now that you have the data for all failed SQL Agent jobs across your database servers on a daily basis, you can create a report for this via Reporting Services and added to your monitoring reports.

There you have it, your first customised data collection set. From here on you can create more monitoring reports for your dashboard and impress your team lead or boss. 🙂



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s


Inspired Global Storytelling

My Time to Travel

The travels of an old(er), solo, woman


The life of a data geek


Traveller Observer

The SQL Pro

(Ayman El-Ghazali -

Meels on Wheels

Meels for breakfast, lunch and dinner.

Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009


Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment

%d bloggers like this: