Log all Activities using sp_WhoIsActive – 2

Hi Friends, in my previous blog post Log all Activities using sp_WhoIsActive, I shared how easy it is to log all sql server sessions using sp_whoIsActive and SQL Server Agent job. Today, let’s do the same logging activity but without Agent job. So below are the steps and queries:-

Step 1: Create Your @destination_table
This step is similar to step 1 of previous blog post, except for the fact that this time we will be using [TempDB] for saving our result. For this make sure to create sp_whoIsActive procedure in tempdb.

This will create table WhoIsActive_ResultSets in tempdb database.

Step 2: Execute TSQL Code to log activities
Copy, and paste below TSQL code in another window. Here modify value of @_endTime variable with End Time for logging activity. Once modified, execute the code. It will continue to run till @_endTime logging queries into tempdb..WhoIsActive_ResultSets every 5 min.

Now, one can find the logged activities by running below query:-

Best part is, query will automatically stop once @_endTime is crossed, or it can be manually stopped by us without any issue.

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

Leave a Reply