Skip to content

sp_HumanEvents problem with tempdb utilization on @event_type = N'blocking', @keep_alive = 1 #143

@JiriDolezalSQL

Description

@JiriDolezalSQL

Version of the script
SELECT
@Version = '2.1',
@version_date = '20210901';

What is the current behavior?
I’ve set up sp_HumanEvents @event_type = N'blocking', @keep_alive = 1 XE session and run EXEC sp_HumanEvents @output_database_name = N'', @output_schema_name = N'dbo' … and left it running.

After 12 hours I had to stop this monitoring because of the high utilization of tempdb that almost filled up the dedicated drive unit.
Running sp_WhoIsActive I’ve seen over 14,000,000 in tempdb_allocations column with sp_HumenEvent session. I didn’t mention what was in tempdb_current.

I had to act quickly (and in a panic) so unfortunately I didn't take the screenshots – I know this would be very valuable.
I just immediately stopped the collection and the XE session and tempdb stopped growing.

If the current behavior is a bug, please provide the steps to reproduce.
I dare not to report this as a bug...maybe it's a wrong usage of sp_HumanEvents - ‚blocking‘ event type is not a good candidate for long-term (days, hours) collection.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?
Microsoft SQL Server 2017 (RTM-CU22-GDR) (KB4583457) - 14.0.3370.1 (X64) Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor)

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions