Log all Activities using sp_WhoIsActive

Spread the love

It is not a new thing for a DBA to check what’s running currently on server in order to figure out issues. My personal favorite tool for finding out current activities is Adam Mechanic’s sp_WhoIsActive. Thanks to SQL Server communities like Brent Ozar Unlimited, we have lot of articles on how to use the proc sp_WhoIsActive. My combination of parameters for proc is as below:-

EXEC master..sp_WhoIsActive @get_plans=1, @get_full_inner_text=1, @get_transaction_info=1, 
@get_task_info=2, @get_locks=1, @get_avg_time=1, @get_additional_info=1,@find_block_leaders=1

Now, it is often our requirement to log running activities to trap execution details for particular time frame. To log all activities of server using sp_WhoIsActive, one can visit Brent’s blog How to Log Activity Using sp_whoisactive in a Loop. I wish to do the same using SQL Agent Job. So below are the steps and queries.

Step 1: Create Your @destination_table
For me, below code from from Brent’s article works perfectly.

use master
DECLARE @destination_table VARCHAR(4000) ;
SET @destination_table = 'WhoIsActive_ResultSets';

DECLARE @schema VARCHAR(4000) ;
--	Specify all your proc parameters here
EXEC master..sp_WhoIsActive @get_plans=1, @get_full_inner_text=1, @get_transaction_info=1, @get_task_info=2, @get_locks=1, @get_avg_time=1, @get_additional_info=1,@find_block_leaders=1,
							@return_schema = 1,
							@schema = @schema OUTPUT ;

SET @schema = REPLACE(@schema, '<table_name>', @destination_table) ;

PRINT @schema
EXEC(@schema) ;

Points to note here is that I am naming my destination table ‘WhoIsActive_ResultSets‘. The table structure will automatically get created based on passed parameters of sp_WhoIsActive. This will create table WhoIsActive_ResultSets in master database.

Step 2: Create Agent Job to Periodically Log Data
Below script can be used to create a job named ‘Log_With_sp_WhoIsActive’. It will be schedule to run every 3 seconds with no end date.

--	Step 2: Create Your Loop to Periodically Log Data
USE [msdb]

/****** Object:  Job [Log_With_sp_WhoIsActive]    Script Date: 12/27/2016 4:55:22 PM ******/
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]    Script Date: 12/27/2016 4:55:22 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Log_With_sp_WhoIsActive', 
		@description=N'This job will log activities using Adam Mechanic''s [sp_whoIsActive] stored procedure.

Results are saved into master..WhoIsActive_ResultSets table.

Job will run every 3 seconds once started.', 
		@category_name=N'[Uncategorized (Local)]', 
		@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Log activities with [sp_WhoIsActive]]    Script Date: 12/27/2016 4:55:22 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @[email protected], @step_name=N'Log activities with [sp_WhoIsActive]', 
		@os_run_priority=0, @subsystem=N'TSQL', 
		@command=N'DECLARE	@destination_table VARCHAR(4000);
SET @destination_table = ''WhoIsActive_ResultSets'';

EXEC master..sp_WhoIsActive @get_plans=1, @get_full_inner_text=1, @get_transaction_info=1, @get_task_info=2, @get_locks=1, @get_avg_time=1, @get_additional_info=1,@find_block_leaders=1,
            @destination_table = @destination_table ;', 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @[email protected], @name=N'Log_Using_whoIsActive_Every_3_Seconds', 
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
GOTO EndSave

Step 3: Query the resultset
Below query can be used to fetch activities for current day.

--	Step 3: Query the resultset
SELECT * FROM WhoIsActive_ResultSets r
	WHERE CAST(r.collection_time AS DATE) = CAST(GETDATE() AS date);
Result Set of [sp_WhoIsActive]
Result Set of [sp_WhoIsActive]

We can adjust recurring intervals from 3 Seconds to any desired time interval. Also, just don’t forgot to perform cleansing after work is done by disabling/removing the job, and truncating or dropping the table. Below is the job history on my machine.

Log Activities using [sp_WhoIsActive]
Log Activities using [sp_WhoIsActive]

Friends, please feel free to correct me by comments. Also, comment if you find the script useful. If you like the article, do Like & Share. Happy Coding 🙂

ads: https://installmentloanline.com


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.