Log all Activities using sp_WhoIsActive
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:-
|
1 2 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
use master go 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) ; GO |
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.
|
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 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 |
-- Step 2: Create Your Loop to Periodically Log Data USE [msdb] GO /****** Object: Job [Log_With_sp_WhoIsActive] Script Date: 12/27/2016 4:55:22 PM ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT 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) BEGIN 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 END DECLARE @jobId BINARY(16) EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Log_With_sp_WhoIsActive', @enabled=1, @notify_level_eventlog=0, @notify_level_email=0, @notify_level_netsend=0, @notify_level_page=0, @delete_level=0, @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 @job_id=@jobId, @step_name=N'Log activities with [sp_WhoIsActive]', @step_id=1, @cmdexec_success_code=0, @on_success_action=1, @on_success_step_id=0, @on_fail_action=2, @on_fail_step_id=0, @retry_attempts=0, @retry_interval=0, @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 ;', @database_name=N'master', @flags=0 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 @job_id=@jobId, @name=N'Log_Using_whoIsActive_Every_3_Seconds', @enabled=1, @freq_type=4, @freq_interval=1, @freq_subday_type=2, @freq_subday_interval=10, @freq_relative_interval=0, @freq_recurrence_factor=0, @active_start_date=20161227, @active_end_date=99991231, @active_start_time=0, @active_end_time=235959, @schedule_uid=N'f583e6cd-9431-4afc-94a3-e3ef9bfa0d27' 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 COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: GO |
Step 3: Query the resultset
Below query can be used to fetch activities for current day.
|
1 2 3 |
-- 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]](https://i0.wp.com/ajaydwivedi.com/wp-content/uploads/2016/12/Log_Activities-02-1.png?ssl=1)
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]](https://i0.wp.com/ajaydwivedi.com/wp-content/uploads/2016/12/Log_Activities-01.png?ssl=1)
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
Related Posts
About The Author
Ajay Dwivedi
I am Microsoft Certified Professional having 10+ years of experience in SQL Server Querying, Database Design, and Administration. I am fond of Query Tuning and like to automate things using TSQL & PowerShell. I also have experience of implementing end-to-end Data Warehouse solution, and Data Migration using ETL tools SQL Server Integration Services (SSIS), Pentaho Business Analytics, and have designed Database Inventory through PowerShell, Python, and Django etc.
Leave a Reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Thank you Ajay… This was really helpful me in proceed work. 🙂
Thank you Ajay.
Suggest you to clean your Agent Job history right away:
EXEC msdb.dbo.sp_purge_jobhistory
@job_name=@YourJobName,
@oldest_date=@TillThisMomentThatYouWantToClean