How to capture sp_whoisactive result set into a table.
The below query will push the data into histroy for our future reference or analysis purpose, we can schedule this query or we can run wehenever we need it.
/* 1) The beloq query will script out sp_whoisactive stored procedure table structure in a print format. I have create table in master database, but we can create it on other database aswell. */ DECLARE @CaptureSchema VARCHAR(MAX) EXEC sp_WhoIsActive @output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%] [cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]', @return_schema = 1, @get_plans = 1, @schema = @CaptureSchema OUTPUT PRINT @CaptureSchema /* The below query will push the data into a table(creating table with the script that the above resultset) please notice @destination_table parameter below. */ EXEC sp_whoisactive @output_column_list = '[dd%][session_id][sql_text][sql_command][login_name][wait_info][tasks][tran_log%] [cpu%][temp%][block%][reads%][writes%][context%][physical%][query_plan][locks][%]', @get_plans=1, @destination_table='master.dbo.sp_whoisactive_history' GO
Comments