Schedule Extended Events Session Start or Stop

Author by Jake Borzym

When creating an Extended Events session, you may want to start it at a later time, or stop it at a specific time. This ability isn't built into the CREATE SESSION statement, but it can easily be accomplished by using ALTER SESSION and SQL Server Agent. The syntax is

ALTER EVENT SESSION session_name 
ON SERVER STATE = {START | STOP}

Let's look at this in action. I have a session on my server, 15SecondIOErrors, which is going to capture reads or writes that take longer than 15 seconds.

XE-schedule-1.png

I know that problems typically appear during my overnight ETL process. I am going to create an Agent Job to start it at 10:00 PM and another Job to stop it at 2:00 am. The first job has one step, which issues the START command.

XE-schedule-22.png

I create a schedule that will execute it during weekdays, for one week.

XE-schedule-3.png

My second job is to stop the session. There is one step, which issues the STOP command. XE-schedule-41.png

The schedule here is very similar, but runs Tuesday to Saturday.

XE-schedule-5.png

This series of steps is how you can schedule the timing of sessions. Interested in learning more about how to get started with Extended Events? Check out my video of Moving from Profiler to Extended Events.  

Author

Jake Borzym

Marketing Manager