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

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

Pre login Handshake or Connection Timeout Period

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server