It is very common that you should investigate what is going on the server at a certain time and you can not find any scheduled job. Yes, the application run a big job. If you want to see current processes, you can run:
|
1 |
EXEC sp_WhoIsActive |
If you want to log it, you can schedule for every 1-10 minutes (you have to set the @destination_table and @destination_database, and the table will be created at the first running time):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
SET NOCOUNT ON; DECLARE @retention INT = 7, @destination_table VARCHAR(500) = 'WhoIsActive', @destination_database sysname = 'SQLMonitor', @schema VARCHAR(MAX), @SQL NVARCHAR(4000), @parameters NVARCHAR(500), @exists BIT; SET @destination_table = @destination_database + '.dbo.' + @destination_table; --create the logging table IF OBJECT_ID(@destination_table) IS NULL BEGIN; EXEC dbo.sp_WhoIsActive @get_full_inner_text = 1, @get_plans = 1, @get_outer_command = 1, @find_block_leaders = 1, @get_transaction_info = 1, @return_schema = 1, @schema = @schema OUTPUT; SET @schema = REPLACE(@schema, '<table_name>', @destination_table); EXEC ( @schema ); END; --create index on collection_time SET @SQL = 'USE ' + QUOTENAME(@destination_database) + '; IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(@destination_table) AND name = N''cx_collection_time'') SET @exists = 0'; SET @parameters = N'@destination_table varchar(500), @exists bit OUTPUT'; EXEC sys.sp_executesql @SQL, @parameters, @destination_table = @destination_table, @exists = @exists OUTPUT; IF @exists = 0 BEGIN; SET @SQL = 'CREATE CLUSTERED INDEX cx_collection_time ON ' + @destination_table + '(collection_time ASC)'; EXEC ( @SQL ); END; --collect activity into logging table EXEC dbo.sp_WhoIsActive @get_full_inner_text = 1, @get_plans = 1, @get_outer_command = 1, @find_block_leaders = 1, @get_transaction_info = 1, @destination_table = @destination_table; --purge older data SET @SQL = 'DELETE FROM ' + @destination_table + ' WHERE collection_time < DATEADD(day, -' + CAST(@retention AS VARCHAR(10)) + ', GETDATE());'; EXEC ( @SQL ); |