Problem
Extended Events (XE) provide the ability to monitor and capture events in a lightweight and customizable way in SQL Server. However, the functionality to start or stop them on a given schedule is not built into them, so if a session that was running is stopped by someone else, it will not start again unless a SQL Server service restart happens or if you want to stop it at a specific time, you cannot do it from the session configuration.
Solution
Since the Extended Event session state can be controlled via T-SQL, with the help of SQL Server Agent Jobs we can monitor if an extended event session is running, and starting it if was stopped, or stop it at a given time.
In this tip we will show you an example for each situation.
T-SQL to Manage SQL Server Extended Events
Before we start, we have to know how to monitor if an extended event session is currently running, this can be achieved using sys.dm_xe_sessions. This DMV show us the active sessions on the instance, in other words: if our session is running there will be a record for it in this object, if it is stopped it will not appear here.
So, we can use the following query to determine if the session is started or stopped.
SELECT
ES.name,
iif(RS.name IS NULL, 0, 1) AS running -- 0 if not is running, 1 if it is running
FROM sys.dm_xe_sessions RS
RIGHT JOIN sys.server_event_sessions ES ON RS.name = ES.name
WHERE es.name = '<YOUR_XE_SESSION_NAME>'
Once we determine the session is started or stopped, we use the following T-SQL to change the state accordingly:
ALTER EVENT SESSION <YOUR_XE_SESSION_NAME>
ON SERVER
STATE = { START | STOP };
GO
With this information we can proceed to perform the logic to control the session status to suit our needs.
Start Extended Event Session if it is Stopped
Imagine that you have a critical XE session you want to be constantly running, and if someone else stops it by mistake you want a process to start it automatically. For this task you create a SQL Server Agent Job to check it. For this example, we will create a job to check for a XE session status every 5 minutes.
First, we create a simple SQL Server Agent Job:
Then in steps, we create a new T-SQL step and put the following code to check an existing event (replace it with the name of your event). You can get rid of the print statements if you want or replace with other logging options.
DECLARE @Status bit;
SELECT
@Status = iif(RS.name IS NULL, 0, 1)
FROM sys.dm_xe_sessions RS
RIGHT JOIN sys.server_event_sessions ES ON RS.name = ES.name
WHERE es.name = 'lock_escalations'
IF( @Status = 0)
BEGIN
print 'It was stopped, starting it...'
ALTER EVENT SESSION lock_escalations
ON SERVER
STATE = START;
print 'XE Session started'
END
ELSE
BEGIN
PRINT 'It is running!'
END
The Job step should look like this:
Configure the other options to suit your needs and then click OK.
On the Schedules, select how often the check must be performed, for this example it will be every 5 minutes. You can adjust this value to suit your needs.
Click OK to save your changes and then configure the other tabs as required for your needs. Then save your job and it is just a matter of testing it.
We stop the Extended Event Session manually as you can see in the image below.
Then if we execute the job, we can see in the log (if you left the print messages) that the session was started.
We can see that it is running again.
For further job executions, we can see that thee validation is done only once and the session is running fine.
We now have a job to monitor that the XE session is running.
Stop the SQL Server Extended Event Session if it is Running
Our second example will be easier, we just want to stop our XE Session at a specific date and time, so again we proceed to create a SQL Server Agent Job.
In the Steps tab, create a new job step and put the following code (replace the name of the XE Session with your own one), also you can get rid of the print statements if you want or put in your own logging controls.
DECLARE @Status bit;
SELECT
@Status = iif(RS.name IS NULL, 0, 1)
FROM sys.dm_xe_sessions RS
RIGHT JOIN sys.server_event_sessions ES ON RS.name = ES.name
WHERE es.name = 'lock_escalations'
IF( @Status = 1)
BEGIN
ALTER EVENT SESSION lock_escalations
ON SERVER
STATE = STOP;
print 'XE Session stopped'
END
The job step should look like this.
Configure the advanced options as you need and then click OK. Then proceed to create a schedule, this will be the time you want to stop the job.
Click OK and configure the other tabs to your needs (maybe you could need an email notification), then save the job and we just need to test it (of course I will not wait 2 weeks, so I put a shorter stop date for testing purposes).
First, we check that the session is running.
Then when the job is executed, we check the log (if you leave the print messages).
And if we check the XE session status, in fact, it is stopped:
Now you can start or stop XE Sessions on demand or programmatically to suit your needs.
Next Steps
- With a little bit more effort you can implement both start and stop steps in the same job using the system date to stop your session for example.
- More than one session can be monitored at once, so you don’t have to create a job for each one.
- You can learn more about DMO sys.dm_xe_sessions on the official Microsoft documentation here.
- You can learn more about DMO sys.server_event_sessions on the official Microsoft documentation here.
- Check my other tip about Extended Events.
