envwhotext

Snippet Name: envwhotext

Tags: Connection Activity

Created Date: Oct 1 2021 8:05AM
Last Modified Date: Apr 1 2022 11:23AM

–desc: sp_whoisactive with transactions and more

Dependencies: This snippet assumes you have installed Adam Machanic’s sp_whoisactive, and leverages some parameters or techniques to drill down into a specific scope.

default description

Sample Output:

 
 --desc: sp_whoisactive with transactions and more
 
 DECLARE @searchString VARCHAR(128) = ''
 
 IF OBJECT_ID('tempdb.[dbo].[#whoisactiveCapture]') IS NOT NULL 
 
 DROP TABLE [dbo].[#whoisactiveCapture] 
 
  CREATE TABLE [#whoisactiveCapture]
 
   (
 
       [dd hh:mm:ss.mss]     VARCHAR (8000)  NULL,
 
       [session_id]          SMALLINT        NOT NULL,
 
       [sql_text]            XML             NULL,
 
       [sql_command]         XML             NULL,
 
       [login_name]          NVARCHAR (128)  NOT NULL,
 
       [wait_info]           NVARCHAR (4000) NULL,
 
       [CPU]                 VARCHAR (30)    NULL,
 
       [tempdb_allocations]  VARCHAR (30)    NULL,
 
       [tempdb_current]      VARCHAR (30)    NULL,
 
       [blocking_session_id] SMALLINT        NULL,
 
       [reads]               VARCHAR (30)    NULL,
 
       [writes]              VARCHAR (30)    NULL,
 
       [physical_reads]      VARCHAR (30)    NULL,
 
       [used_memory]         VARCHAR (30)    NULL,
 
       [status]              VARCHAR (30)    NOT NULL,
 
       [tran_start_time]     DATETIME        NULL,
 
       [open_tran_count]     VARCHAR (30)    NULL,
 
       [percent_complete]    VARCHAR (30)    NULL,
 
       [host_name]           NVARCHAR (128)  NULL,
 
       [database_name]       NVARCHAR (128)  NULL,
 
       [program_name]        NVARCHAR (128)  NULL,
 
       [start_time]          DATETIME        NOT NULL,
 
       [login_time]          DATETIME        NULL,
 
       [request_id]          INT             NULL,
 
       [collection_time]     DATETIME        NOT NULL
 
   );
 
 
 
   EXECUTE [dbo].[sp_WhoIsActive]
 
             @get_outer_command=1,
 
             @output_column_list   = '[dd hh:mm:ss.mss],[session_id],[sql_text],[sql_command],[login_name],[wait_info],[CPU],[tempdb_allocations],[tempdb_current],[blocking_session_id],[reads],[writes],[physical_reads],[used_memory],[status],[tran_start_time],[open_tran_count],[percent_complete],[host_name],[database_name],[program_name],[start_time],[login_time],[request_id],[collection_time]',
 
             @get_full_inner_text  = 1,
 
             @show_sleeping_spids  = 2,
 
             @get_transaction_info = 1,
 
             @show_system_spids    = 0,
 
             @destination_table    = '[#whoisactiveCapture]';
 
 
 
  SELECT  [TransactionElapsedTime dd hh:mm:ss.mss] = dt.Days + ':' + dt.Hours + ':' + dt.Minutes + ':' + dt.Seconds,
 
 DATEDIFF(SECOND,[tran_start_time],GETDATE()) AS TransactionTimeInSeconds,*
 
   FROM #whoisactiveCapture
 
       CROSS APPLY(SELECT [Days]          = RIGHT('000' + CONVERT(VARCHAR(128),datepart(day,GETDATE() - [tran_start_time])-1),2),
 
                        [Hours]         = RIGHT('000' + CONVERT(VARCHAR(128),DATEPART(Hour,GETDATE() - [tran_start_time])),2),
 
                        [Minutes]       = RIGHT('000' + CONVERT(VARCHAR(128),datepart(Minute,GETDATE() - [tran_start_time])),2),
 
                        [Seconds]       = RIGHT('000' +CONVERT(VARCHAR(128),datepart(Second,GETDATE() - [tran_start_time])),2)
 
                ) dt
 
 WHERE session_id > 50
 
 AND CONVERT(VARCHAR(MAX),[sql_test]) LIKE '%' + @searchString + '%'
 
 --ORDER BY 1 DESC--ORDER BY 1 DESC