4

I have a process which starts and stops Extended Event sessions in order to transfer data from event files into SQL tables. On a SQL Server 2022 RTM server, I see many lines written to the log files by this process:

enter image description here

I would like the starting and stopping of XE sessions to not be written to the log file because my log files are now overwhelmingly filled with these entries. This behavior does not occur on a Microsoft SQL Server 2019 (RTM-CU22-GDR) instance.

How can I prevent SQL Server from logging the starting and stopping of XE sessions to the server log files?

1
  • Why are you starting and stopping sessions that often? Commented Apr 15 at 17:56

1 Answer 1

10

Readers Note: This was done with public symbols from the Microsoft Symbols Server.


Update: These messages should no longer, by default, be written to the errorlog as of SQL 2022 CU15. If you'd still like them for tracking, you can use a traceflag to have them written to the errorlog.


The errorlog start and stop items are being generated in the XEventController::AlterEventSession and XEventController::VLR_StopXESession::CommitAndRelease functions, respectively, in sqllang.dll for SQL Server 2022 RTM.

Start Writes

Looking at the start function first, which is a very large function, the call before 0x107e:

sqllang!XEventController::AlterEventSession+0x106d:
00007ff8`d1fa940d 488d95c80a0000  lea     rdx,[rbp+0AC8h]
00007ff8`d1fa9414 b999640000      mov     ecx,6499h
00007ff8`d1fa9419 e8a2f62301      call    sqllang!scierrlog 

The mov into ecx is loading up 0x6499 to pass to sqllang!scierrlog which we could deduce is the error number to write the message, which indeed 0x6499 (0n25753) matches with the public documentation.

25753 XE session '%s' started.

Looking at items that may run the code, there is a single branch which points that way at sqllang!XEventController::AlterEventSession+0x101f

00007ff8`d1fa93d5 488d542468      lea     rdx,[rsp+68h]
00007ff8`d1fa93da 488d8d900a0000  lea     rcx,[rbp+0A90h]
00007ff8`d1fa93e1 ff1551e52a02    call    qword ptr [sqllang!XE_API::sm_ClientAPI+0xa8 (00007ff8`d4257938)]
00007ff8`d1fa93e7 85c0            test    eax,eax
00007ff8`d1fa93e9 7522            jne     sqllang!XEventController::AlterEventSession+0x106d

There doesn't seem to a trace flag or other easily set check, in fact it seems dependent upon the return of a function pointer at sqllang!XE_API::sm_ClientAPI+0xa8. I would say for the start errorlog item, there wouldn't be an easily configurable way to turn it off.

Given that you now know where to find this, you could inject your own code and NOP (or jmp) the call to scierrlog which clearly would be unsupported.

Looking at a SQL Server 2019 CU24 instance, the same function has no references to the errorlog and has many differing changes. It seems as though additions were made in 2022 to this function which were not backported to 2019.

Stop Writes

It's interesting that the stop isn't in the same function, given the start was in AlterEventSession which one would think would be used for ALTER T-SQL commands for event sessions 🤷‍♂️

Luckily sqllang!XEventController::VLR_StopXESession::CommitAndRelease is a much smaller function. Using what we learned from the previous function investigation, there will be a loading of ecx with the corresponding error number and then a call to scierrlog. Sure enough, it's there and very easy to spot right at the beginning of the function!

00007ff8`d1fa8105 488d9570020000  lea     rdx,[rbp+270h]
00007ff8`d1fa810c 488b4938        mov     rcx,qword ptr [rcx+38h]
00007ff8`d1fa8110 ff152af82a02    call    qword ptr [sqllang!XE_API::sm_ClientAPI+0xb0 (00007ff8`d4257940)]
00007ff8`d1fa8116 488d95a8020000  lea     rdx,[rbp+2A8h]
00007ff8`d1fa811d b99a640000      mov     ecx,649Ah
00007ff8`d1fa8122 e899092401      call    sqllang!scierrlog

Here we can see in ecx the 32-bit value of 0x649A (0n25754) loaded and then a call to scierrlog, curiously before this there is a similar call to sqllang!XE_API::sm_ClientAPI but with a much different offset. Regardless, there is no conditional test around the return of the call, so it will always write this to the errorlog and doesn't seem to be gated by a traceflag or other mechanism at all. This is much more straight forward that the start assembly.

25754 XE session '%s' stopping.

The same option applies here, overwrite the call instruction with NOPs.

Checking this function against the same SQL 2019 CU24 instance previously mentioned also shows quite a bit of differing code at the start of the function, which again, doesn't seem to have the errorlog call nor be backported (for better or worse).

Most Effort

A way that could be arguably supported would be to make a very small filesystem filter driver to check for file writes specifically to the errorlog file and check to see if the data being written is something you really do not want. You could then quietly throw it away and no one would know, the log wouldn't show it, and hopefully the filter driver wouldn't have bugs or break anything else in the environment.

Least Effort

Open a support ticket with Microsoft and ask that a product change be made for an option to turn off the logging of the messages so that there is a supported way.

2
  • Great post Sean! Are you willing to share an unofficial opinion regarding the odds of a support ticket being successful? If you want to send it privately that's fine too.
    – Joe Obbish
    Commented Mar 13 at 19:05
  • @JoeObbish Hopefully it helped :) Finger to the wind, I would say the change request will definitely be created but I don't have high hopes for it making it into a CU anytime soon. Even assuming it would be in the next CU that'd still be months away given the recent CU release dates for the past year :/ Commented Mar 13 at 19:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.