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:-

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.

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 3: Query the resultset
Below query can be used to fetch activities for current day.

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 🙂

One Comment

Leave a Reply