SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Schedule an Extended Event to run at a Specific Time

When you are using the extended event to capture some data for troubleshooting purpose, you may want to start or stop it at a specific time. Unfortunately, the Extended Event feature doesn’t provide you the option to schedule it. But, it can easily be accomplished by using “ALTER EVENT SESSION” T-SQL code and SQL Server Agent. You can use the below syntax to start and stop the xEvent session;

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

Demonstration:

To demonstrate it, I have created an extended event session “SP_Performance” on the server which is going to capture the provided stored procedure execution duration by the statements.

I know from the application team that the SP performance goes worst especially in the evening time so I am going to create an SQL Agent Job to start it at 3 PM and another SQL Job to stop it at 6 PM.

The first job has one step, which issues the START command.

My second job is to stop the session. There is one step, which issues the STOP command.

Here is schedules detail of the jobs;

Hope, you enjoyed learning of how you can schedule the timing of xEvent sessions.

The post Schedule an Extended Event to run at a Specific Time appeared first on .

SQL Geek

Dharmendra is a SQL Server/Microsoft Data Platform professional with over eight years of experience. He enjoys helping others in the SQL Server community and does this by contributing on blogs, speaking at several SQL events. His passion and focus is to explore and share more and more on SQL Server.

Comments

Leave a comment on the original post [www.dharmendrakeshari.com, opens in a new window]

Loading comments...