Only Capture Extended Events For a Given Time

It’s a great question. Let’s say you want to capture stored procedure completions. But, you only want to capture them between 3AM and 4AM. Can you do it?

Output of rpc_completed

Let’s create a really simple event:

CREATE EVENT SESSION [RPCTimeBoxed] ON SERVER 
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
    WHERE ([sqlserver].[database_name]=N'AdventureWorks'));

If we start this event, run some code, the output within the Data Explorer window looks like this:

Just a couple of points here. Notice the fields in the event. None of them are dates or times. However, up above, we get the timestamp column. Done, right? Let’s use that.

But first, what does AI, through CoPilot tell me?

CoPilot To The Rescue?

I asked CoPilot. It took a couple of refinements to get it on board with the idea that I wanted to filter at capture, not after I’ve already got the events. It supplied me with this example script:

CREATE EVENT SESSION [SessionName] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed
(
    SET collect_statement=(1)
    WHERE ([timestamp] > '2024-06-27 08:00:00.000' AND [timestamp] < '2024-06-27 09:00:00.000')
)

Ignoring for the moment the fact that it left off the semi-colon, <grrrrr>, it looks like a fine script. I’d rather use rpc_completed, but hey, for testing purposes, sql_statement_completed is fine. Here’s the output of this script:

Msg 25706, Level 16, State 8, Line 12
The event attribute or predicate source, “timestamp”, could not be found.

Completion time: 2024-06-27T10:04:32.3495767-05:00

And if you look through the Extended Events gui, on the Configure page of the Events tab, under Filter (Predicate), you get a list as shown here:

If you scroll through all that, nowhere will you see ‘timestamp’. Further, no other values similar to timestamp present themselves as a part of the event, package or any of the actions.

In short, you can’t. Despite the bad info from the AI.

What Do You Do Instead?

OK. Fine, we can’t filter by timestamp and nothing else is immediately available, so what do we do?

Schedule start and stop times. Run this to start it:

ALTER EVENT SESSION RPCTimeBoxed ON SERVER STATE = START;

Run this to stop it:

ALTER EVENT SESSION RPCTimeBoxed ON SERVER STATE = STOP;

Use your favorite scheduling engine to set the times to make this happen.

Conclusion

Yeah, I know. Disappointing answer. But hey, sometimes, there just isn’t a way to do things the way we want. So, you find another way rather than curse & spit.

I guess we file this under “Things you can’t do, easily, with Extended Events”

UPDATE: It was rightly pointed out to me that I didn’t thank some people on X who validated what I already believed, that there wasn’t a way to do this. Let’s fix that now. Thanks Erik Darling, Tom Zika, and Daniel Maenle for the confirmation.