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’,
@collection_mode=1,
@description=N’Collects number of rows in each table daily’,
@logging_level=0,
@days_until_expiration=180,
@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 < 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,
@collection_set_id=@collection_set_id_1,
@collector_type_uid=@collector_type_uid_3
— 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 @ErrorLine INT;
DECLARE @ErrorProcedure NVARCHAR(200);
SELECT @ErrorLine = ERROR_LINE(),
@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;
GO