SQL Server exception running sp_WhoIsActive regularly

[philcart]

Hi All

Hope someone is able to provide some insight and potential resolution for a problem we're experiencing

This problem has showed up on three servers now. Each occurrence has started with an exception being generated by the SPID running sp_WhoIsActive. There isn't any specific timeline (runtime, time of day, etc...) and the workloads on each of the servers is very different. On two occasions the SQL Server process was terminated, and another the service had to be manually restarted as it was in a CPU spin. Other occasions have left the server generally unresponsive until the offending SPID was killed off.

Running

• sp_WhoIsActive 11.35 on
• Windows Server Standard 2019
• SQL Server 2019 (15.0.4102.2)

sp_WhoIsActive is being run in a SQL Agent job to capture any blocking activity occurring on the server. The job runs every minute and has two steps, 1) Capture sp_WhoIsActive data, 2) Analyse and raise an alert if blocking is detected.

The command to capture the data is as follows,

EXEC [dbo].[sp_WhoIsActive]
    @get_additional_info = 1
    , @get_full_inner_text = 1
    , @get_plans = 2 
    , @find_block_leaders = 1
    , @output_column_list = '[collection_time][session_id][blocking_session_id][open_tran_count][status][dd hh:mm:ss.mss][start_time][login_time][login_name][database_name][host_name][program_name][sql_text][query_plan][wait_info][reads][writes][CPU][additional_info]' 
    , @DESTINATION_TABLE = 'dbo.workBlockingCapture';

This is the structure of the workBlockingCapture table,

CREATE TABLE [dbo].[workBlockingCapture](
    [collection_time] [datetime] NOT NULL,
    [session_id] [smallint] NOT NULL,
    [blocking_session_id] [smallint] NULL,
    [open_tran_count] [varchar](30) NULL,
    [status] [varchar](30) NOT NULL,
    [dd hh:mm:ss.mss] [varchar](8000) NULL,
    [start_time] [datetime] NOT NULL,
    [login_time] [datetime] NULL,
    [login_name] [nvarchar](128) NOT NULL,
    [database_name] [nvarchar](128) NULL,
    [host_name] [nvarchar](128) NULL,
    [program_name] [nvarchar](128) NULL,
    [sql_text] [xml] NULL,
    [query_plan] [xml] NULL,
    [wait_info] [nvarchar](4000) NULL,
    [reads] [varchar](30) NULL,
    [writes] [varchar](30) NULL,
    [CPU] [varchar](30) NULL,
    [additional_info] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The following the latest exception event that occurred,

2021-08-15 12:41:00.41 spid62      CImageHelper::Init () Version-specific dbghelp.dll is not used
2021-08-15 12:41:00.43 spid62      Using 'dbghelp.dll' version '4.0.5'
2021-08-15 12:41:02.17 spid62      ***Stack Dump being sent to D:\MSSQL15.MSSQLSERVER\MSSQL\LOG\SQLDump10000.txt
2021-08-15 12:41:02.18 spid62      SqlDumpExceptionHandler: Process 62 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
2021-08-15 12:41:02.18 spid62      * *******************************************************************************
2021-08-15 12:41:02.18 spid62      *
2021-08-15 12:41:02.18 spid62      * BEGIN STACK DUMP:
2021-08-15 12:41:02.18 spid62      *   08/15/21 12:41:02 spid 62
2021-08-15 12:41:02.18 spid62      *
2021-08-15 12:41:02.18 spid62      *
2021-08-15 12:41:02.18 spid62      *   Exception Address = 00007FF82A47FCBD Module(sqllang+000000000003FCBD)
2021-08-15 12:41:02.18 spid62      *   Exception Code    = c0000005 EXCEPTION_ACCESS_VIOLATION
2021-08-15 12:41:02.18 spid62      *   Access Violation occurred reading address 0000021EDB971288
2021-08-15 12:41:02.19 spid62      * Input Buffer 510 bytes -
2021-08-15 12:41:02.20 spid62      *             EXEC [dbo].[sp_WhoIsActive]    @get_additional_info = 1    , @g
2021-08-15 12:41:02.20 spid62      *  et_full_inner_text = 1    , @get_plans = 2    , @find_block_leaders = 1  
2021-08-15 12:41:02.20 spid62      *    , @output_column_list = '[collection_time][session_id][blocking_session
2021-08-15 12:41:02.20 spid62      *  _id][open_tran_count][status][dd hh:mm:ss.mss][start_time][login_time][l
2021-08-15 12:41:02.20 spid62      *  ogin_name][database_name][host_name][program_name][sql_text][query_plan]
2021-08-15 12:41:02.20 spid62      *  [wait_info][reads][writes][CPU][additional_info]'     , @DESTINATION_TABL
2021-08-15 12:41:02.20 spid62      *  E = 'dbo.workBlockingCapture';

If any further details are required, I'm happy to provide what I'm able to.

Cheers Phil

[erikdarlingdata]

@philcart this is a SQL Server bug that has impacted several analysis procs and monitoring tools. You should open a ticket with Microsoft.

[osudude]

@erikdarlingdata, similar problem here after upgrading from SQL 2014 to SQL2019. Didn't have this problem in 2014.

Who Is Active? v12.00 (2021-11-10) Microsoft SQL Server 2019 (RTM-CU14) (KB5007182) - 15.0.4188.2 (X64) Exception Address = 00007FFE77EE3C1A Module(sqlmin+0000000000033C1A) 2022-02-27 19:42:31.13 spid604 Exception Code = c0000005 EXCEPTION_ACCESS_VIOLATION 2022-02-27 19:42:31.13 spid604 Access Violation occurred reading address 0000000000000030 2022-02-27 19:42:31.13 spid604 Input Buffer 92 bytes - 2022-02-27 19:42:31.13 spid604 EXEC [dbo].[sp_CaptureWhoIsActive]

[212614058]

CU15 solved this issue for me @erikdarlingdata .