Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
SQL Server Transparent Data Encryption Alternative - Free Webinar
 

Start and Stop Extended Events Sessions Automatically Using SQL Server Agent Jobs


By:   |   Last Updated: 2018-11-02   |   Comments   |   Related Tips: More > Extended Events

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:

job creation 1

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:

job step creation 1

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.

job schedule creation 1

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.

session status stopped

Then if we execute the job, we can see in the log (if you left the print messages) that the session was started.

job execution log 1

We can see that it is running again.

session status started

For further job executions, we can see that thee validation is done only once and the session is running fine.

job execution log

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.

job creation

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.

job step creation

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.

job schedule creation

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.

XE session is running

Then when the job is executed, we check the log (if you leave the print messages).

job results from execution

And if we check the XE session status, in fact, it is stopped:

session 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.


Last Updated: 2018-11-02


next webcast button


next tip button



About the author
MSSQLTips author Eduardo Pivaral Eduardo Pivaral is an MCSA, SQL Server Database Administrator and Developer with over 15 years experience working in large environments.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools