[BUG] SQL 2016 in single DB in mirroring setup

[jaywiseman1971]

Component

SQL collection scripts

Performance Monitor Version

2.0.0

SQL Server Version

SQL 2016

Windows Version

Windows 2019

Describe the Bug

Getting SQL dumps consistently on the passive server in the mirror pair of a database. Had to stop the PerformanceMonitor - Collection job to stop the SQL dumps.

Steps to Reproduce

Run collection job on a passive server on a mirrored database.

Expected Behavior

No dumps

Actual Behavior

Creates 25 dumps per each collection run.

Error Messages / Log Output

One collection job run produces 25 .log and .mdmp dumps.

Screenshots

SQLDump0001.log

SQLDump0002.mdmp.TXT

Additional Context

I did re-install v2.0.0 without any issues or any changes occurred with the script, problem still happening.

[erikdarlingdata]

Thanks for reporting this — sorry about the dumps, that's a bad time.

Root Cause

Several collectors call OBJECT_NAME(object_id, database_id) and OBJECT_SCHEMA_NAME(object_id, database_id) against plan cache entries. These functions open the target database's metadata catalog to resolve the object name. On a mirroring passive server, the mirrored database is in RESTORING state and can't be opened — this causes a severity 22 engine access violation and a SQL dump.

The stack dump you shared confirms this: major=52, minor=42, state=9, severity=22 on SPID 32.

The primary offender is collect.procedure_stats_collector, which calls OBJECT_NAME/OBJECT_SCHEMA_NAME across three sub-queries (procedures, triggers, functions) — each one would crash when hitting a cached plan from the RESTORING database.

Fix (PR #385, merged to dev)

• procedure_stats: Changed LEFT JOIN to INNER JOIN sys.databases with a d.state = 0 (ONLINE) filter on all 3 sub-queries. This structurally excludes RESTORING database rows from the result set so OBJECT_NAME/OBJECT_SCHEMA_NAME are never evaluated for them.
• query_stats: Added d.state = 0 filter (defensive — no OBJECT_NAME calls but excludes irrelevant data from RESTORING databases).
• file_io_stats and waiting_tasks: Replaced DB_NAME(database_id) function calls with a LEFT JOIN sys.databases using d.name instead.

Next Steps

This fix will be in the next release. In the meantime, you can grab the updated install scripts from the dev branch and re-run them on your server to deploy the fixed collector procedures:

• install/10_collect_procedure_stats.sql
• install/08_collect_query_stats.sql
• install/20_collect_file_io_stats.sql
• install/37_collect_waiting_tasks.sql

Also — could you share your SQL Server 2016 patch level (SELECT @@VERSION)? Microsoft may have fixed the underlying engine crash in a CU, and it would be good to know if you're on the latest SP/CU.

[jaywiseman1971]

SQL Version = Microsoft SQL Server 2016 (SP3-CU1-GDR) (KB5029187) - 13.0.7029.3 (X64) Aug 16 2023 19:44:44 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: )

I will wait till the next release, I just disabled the collection job and it stopped the dumps.

[erikdarlingdata]

Thanks for the version info. SP3-CU1 is a few CUs behind the latest SP3 (CU17+GDR), but this particular crash is fundamental — OBJECT_NAME against a RESTORING database will always fail regardless of patch level. The fix is in our query structure, not a SQL Server CU.

Disabling the job is the right call until the next release. We'll get this out soon.

[jaywiseman1971]

few CUs behind the latest SP3 (CU17+GDR),

I don't think my SQL returned the correct CU values, because of the version I'm seeing next after it.

13.0.7029.3

Based on your list, the .7029. is a versioning number that keeps going up and I'm pretty good applying patches.

https://sqlserverupdates.com/sql-server-2016-updates/

The highest this number is on the site above is 13.0.6475.1 which I'm above on CU's and GDRs.

Correct?

Best, Jay

[erikdarlingdata]

Fixed in PRs #385 and #386 — INNER JOIN + d.state = 0 filter prevents OBJECT_NAME from opening RESTORING database metadata.