Creating Your Own Custom Data Collections

After I script the “Disk Usage” Data Collection I need to modify it to meet my data collection requirements.  Here is the script I created from the cloned “Disk Usage” script.  This script when executed will creating my custom Data Collection.  I have broken this script up into chucks by creating “Begin” and “End” sections with comments in the code.  I will explain later what each of these sections are doing. 

Begin Transaction
Begin Try
Declare @collection_set_id_1 int
Declare @collection_set_uid_2 uniqueidentifier

– Begin Section A —————————————
EXEC [msdb].[dbo].[sp_syscollector_create_collection_set]
    @name=N’Number of Rows’,
    @description=N’Collects number of rows in each table daily’,
    @schedule_name=N’Daily at Midnight’,
    @collection_set_id=@collection_set_id_1 OUTPUT,
    @collection_set_uid=@collection_set_uid_2 OUTPUT
– End Section A —————————————-

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

– Begin Section C ————————————–
Declare @collection_item_id_4 int
EXEC [msdb].[dbo].[sp_syscollector_create_collection_item]
    @name=N’Get Number of Rows’,
    @parameters=N’<ns:TSQLQueryCollector xmlns:ns=”DataCollectorType”><Query><Value>
SELECT OBJECT_NAME(o.object_id) ObjectName
     , SCHEMA_NAME(o.schema_id) SchemaName
     , SUM(p.Rows) NumOfRows
FROM sys.partitions p
JOIN sys.objects o ON p.object_id = o.object_id
WHERE Index_ID &lt; 2 AND o.type = ”U”
GROUP BY SCHEMA_NAME(o.schema_id), OBJECT_NAME(o.object_id) 
ORDER BY SCHEMA_NAME(o.schema_id), OBJECT_NAME(o.object_id)
</Value><OutputTable>NumOfRows</OutputTable></Query><Databases UseSystemDatabases=”true” UseUserDatabases=”true” /></ns:TSQLQueryCollector>’,
    @collection_item_id=@collection_item_id_4 OUTPUT,
– End Section C —————————————-

Commit Transaction;
End Try
Begin Catch
Rollback Transaction;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorNumber INT;
DECLARE @ErrorProcedure NVARCHAR(200);
       @ErrorSeverity = ERROR_SEVERITY(),
       @ErrorState = ERROR_STATE(),
       @ErrorNumber = ERROR_NUMBER(),
       @ErrorMessage = ERROR_MESSAGE(),
       @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), ‘-’);
RAISERROR (14684, @ErrorSeverity, 1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);

End Catch;



Pages: 1 2 3 4


No comments yet... Be the first to leave a reply!