3JPJMgZP

1SET NOCOUNT ON;
2
3DECLARE @retention INT = 7,
4        @destination_table VARCHAR(500) = 'WhoIsActive',
5        @destination_database sysname = 'master',
6        @schema VARCHAR(MAX),
7        @SQL NVARCHAR(4000),
8        @parameters NVARCHAR(500),
9        @exists BIT;
10
11SET @destination_table = @destination_database + '.dbo.' + @destination_table;
12
13--create the logging table
14IF OBJECT_ID(@destination_table) IS NULL
15    BEGIN;
16        EXEC dbo.sp_WhoIsActive @get_transaction_info = 1,
17                                @get_outer_command = 1,
18                                @get_plans = 1,
19                                @return_schema = 1,
20                                @schema = @schema OUTPUT;
21        SET @schema = REPLACE(@schema, '<table_name>', @destination_table);
22        EXEC ( @schema );
23    END;
24
25--create index on collection_time
26SET @SQL
27    = 'USE ' + QUOTENAME(@destination_database)
28      + '; IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(@destination_table) AND name = N''cx_collection_time'') SET @exists = 0';
29SET @parameters = N'@destination_table varchar(500), @exists bit OUTPUT';
30EXEC sys.sp_executesql @SQL, @parameters, @destination_table = @destination_table, @exists = @exists OUTPUT;
31
32IF @exists = 0
33    BEGIN;
34        SET @SQL = 'CREATE CLUSTERED INDEX cx_collection_time ON ' + @destination_table + '(collection_time ASC)';
35        EXEC ( @SQL );
36    END;
37
38--collect activity into logging table
39EXEC dbo.sp_WhoIsActive @get_transaction_info = 1,
40                        @get_outer_command = 1,
41                        @get_plans = 1,
42                        @destination_table = @destination_table;
43
44--purge older data
45SET @SQL
46    = 'DELETE FROM ' + @destination_table + ' WHERE collection_time < DATEADD(day, -' + CAST(@retention AS VARCHAR(10))
47      + ', GETDATE());';
48EXEC ( @SQL );
49
50/*
51SELECT TOP 1000 CPU, reads, collection_time, start_time, [dd hh:mm:ss.mss] AS 'run duration', [program_name], login_name, database_name, session_id, blocking_session_id, wait_info, sql_text, *
52FROM master.dbo.WhoIsActive
53*/