Monitoring RCSI

I created a sql-job to run every 10 minutes to a) save the current ghost count, and b) email me if its a new high!

Step-1 create the table

CREATE TABLE [maint_db].[dbo].[rcsi_monitor] 
	(
	date_time DATETIME, 
	table_name VARCHAR(50), 
	ghost_records BIGINT
	);

If this step succeeded the job would end there. If the step failed (-say- because the table already existed) the job would continue to step-2

Step-2 save the current counts to the table

INSERT INTO [maint_db].[dbo].[rcsi_monitor]

SELECT	GETDATE(),
	OBJECT_NAME(object_id),
	version_ghost_record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, 'sampled')
WHERE version_ghost_record_count > 0;

The SELECT statement above is the only novel thing here, and perhaps the most useful take-away. (Note: the DB_ID() means the current database, so ensure it runs under the right one).

Step-3 send an alert – if the current count is the new HIGH SCORE!

IF 
	(SELECT MAX(version_ghost_record_count) FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, 'sampled'))
>=
	(SELECT ISNULL(MAX(ghost_records), 0) FROM [maint_db].[dbo].[rcsi_monitor])
AND
	(SELECT ISNULL(MAX(ghost_records), 0) FROM [maint_db].[dbo].[rcsi_monitor]) > 0
BEGIN
	RAISERROR ('Too many Ghost! AAAAAaaarrrrrrggggghh!', 16, 1)
	RETURN
END

The RAISERROR and RETURN would force the job to fail, triggering an email via Notifications.