Long Queries Stops Collecting Randomly

Did you check DOCS to make sure there is no workaround? Yes, did not see anything, unsure how to troubleshoot beyond log table and event viewer.

Describe the bug Randomly the XES long queries will stop collecting, even though the SQL Server agent job shows no failures. I have even manually executed dbo.usp_sqlwatch_logger_xes_long_queries which shows no issues/errors. Additionally, I have queried the dbo.sqlwatch_logger_xes_long_queries table and it has stopped loading new records. However, other types of metrics/tables when querying those tables show they are being collected, so it appears to be just long queries.

To Reproduce Unsure how to reproduce. I can typically resolve the issue by right-clicking the database > tasks > delete data-tier application > confirm delete Then I redeploy the dacpac via sqlpackage.exe and it is back working again.

Expected behavior I expect the long queries to always collect with no intervention needed.

Screenshots Job history for SQLWATCH-LOGGER-XES agent job:

Grafana chart looking at long query averages shows no more data:

Windows Server (please complete the following information):

OS Version: Windows Server 2016 Standard

SQL Server (please complete the following information):

SQL Version: SQL Server 2017
SQL Edition: Standard

SQL Server Management Studio (SSMS -> about -> copy info): Microsoft SQL Server Management Studio 14.0.17289.0 Microsoft Analysis Services Client Tools 14.0.1016.283 Microsoft Data Access Components (MDAC) 10.0.14393.6343 Microsoft MSXML 3.0 6.0 Microsoft Internet Explorer 9.11.14393.0 Microsoft .NET Framework 4.0.30319.42000 Operating System 6.3.14393

SQLWATCH version (from DACPAC or from sysinstances)

Additional context I'm puzzled on how to troubleshoot this, I don't see anything glaring in the dbo.sqlwatch_app_log table nor in the event viewer. How can I dig into the problem here? Thanks!

marcingminski / sqlwatch

Long Queries Stops Collecting Randomly #486