Failure of SQLWATCH-LOGGER-XES and SQLWATCH-LOGGER-PERFORMANCE in v4.3

[mike-halford]

Since upgrade to v4.3 both SQLWATCH-LOGGER-XES and SQLWATCH-LOGGER-PERFORMANCE fail with following error

07/22/2021 09:37:53,SQLWATCH-LOGGER-XES,Error,1,???\???,SQLWATCH-LOGGER-XES,dbo.usp_sqlwatch_logger_xes_waits,,Executed as user: ???. The log file name "(null)" is invalid. Verify that the file exists and that the SQL Server service account has access to it. [SQLSTATE 42000] (Error 25718) The statement has been terminated. [SQLSTATE 01000] (Error 3621). The step failed.,00:00:00,16,3621,,,,0

Was working fine in v4.2.

Environment SQL Server 2017 v14.0.3391.2 SQLWATCH.4.3.0.23725.20210721131116

[marcingminski]

There have been no changes to either of those in 4.3. Where it says, "log file name", does it refer to the xes file? Do the xe sessions exist? Do the Sql account and agent account do in fact have access to the xes files?

[mike-halford]

No log file name is visible in the error . The only things masked in error message are server and agent account otherwise error message is as generated by job failure.

Excuse my ignorance by not sure how to tell if xe sessions exist or not?

[marcingminski]

Its in Management -> Extended Events -> Sessions:

[mike-halford]

Lookes like sessions exist

[marcingminski]

what happens if you run procs in ssms manually rather than via agent?

[mike-halford]

When proc is run directly from SSMS the same error results

Msg 25718, Level 16, State 3, Procedure dbo.usp_sqlwatch_internal_get_xes_data, Line 83 [Batch Start Line 2] The log file name "(null)" is invalid. Verify that the file exists and that the SQL Server service account has access to it.

[marcingminski]

are you able to deploy 4.2 alongside as a new database, say SQLWATCH_4_2 and retry? You can download 4.2 here: https://github.com/marcingminski/sqlwatch/releases/tag/4.2, unzip and deploy dacpac in SSMS: https://docs.sqlwatch.io/installation/alternative-installation/#management-studio

As there is only one set of extended events sessions, both databases will use the same file and sessions.

[mike-halford]

I haver installed v4.2 along side and am getting the same errors on that version now. Not really sure whats going on.

Sorry for the confusion I was certain it was not failing prior to upgrade. Can I suggest we close the call until I can investigate the server further.

Thanks

[marcingminski]

I am 99% sure its your SQL account permissions. However, once you figured it out please do report back and I will document it. It's a good example to document!

[mike-halford]

Will report back after further investigation.

Thanks again for all your help.

[marcingminski]

Just as a suggestion - in case something had gone wrong with xe sessions - are you able to actually view the content of the file in SSMS. If you expand the session and right click on the package0.event_file:

[mike-halford]

Not sure this is significant but I do not see package0.event.file I only see package0.ring_buffer under the SQLWATCH_blockers

[marcingminski]

ah. so that explains it. This means the data sits in the ring buffer "memory" and not the file. I wonder if you have had these since early version of SQLWATCH?

The standard deployment does not actually update extended event sessions (it's how MS designed it) and it's something I am addressing in version 5.0

Can you delete all these SQLWATCH sessions and recreate? You can recreate by redeploying SQLWATCH or you can get the code here: https://github.com/marcingminski/sqlwatch/tree/main/SqlWatch.Monitor/Project.SqlWatch.Database/dbo/Extended%20Events to deploy manually

[mike-halford]

Bingo. All sorted. Its just a test server so I just deleted the sessions then redeployed and the problem has gone away. As a side issue deployed the new version to a clean server (never had SQLWATCH installed) and it worked first time.

Thanks again for all your help.