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.

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

DECLARE @schema VARCHAR(4000) ;
--	Specify all your proc parameters here
EXEC tempdb..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) ;

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.

USE tempdb
DECLARE @_endTime DATETIME2 = '2017-05-04 08:30:48.280'; --Server Time

WHILE GETDATE() <= @_endTime -- if current time is less than end time

	EXEC tempdb..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 = 'WhoIsActive_ResultSets' ;

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

SELECT * FROM WhoIsActive_ResultSets with (nolock) ORDER BY collection_time DESC;

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

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