Create SQL Server Extended Events Session Using SSMS PowerShell Provider



By:
Overview

Similar to using TSQL we can create an Extended Events session programmatically using the PowerShell Provider.  To start PowerShell simply type sqlps from a command prompt or if you prefer using SSMS, open the Object Explorer and expand the "Management" node and right-click "Extended Events" and select "Start PowerShell" as shown below.

start powershell from SSMS

Alternatively, you can start a regular PowerShell session using any method you are familiar with but in this case you will also have to import the sqlserver module so all the cmdlets that support the SQL Server features are available.

Once you have started a PowerShell session you can switch to the XEvent folder under the SQLSERVER drive and list all the packages and sessions configured in you SQL Server instance.

PS SQLSERVER:\XEvent\home-pc\DEFAULT> dir Sessions
Name                 AutoStartUp          IsRunning            Start Time
----                 -----------          ---------            ----------
AlwaysOn_health      False                False
First_XEvent_Session False                False
First_XEvent_Sess... False                False
First_XEvent_Sess... False                False
system_health        True                 True                 5/8/2019 9:45 AM
telemetry_xevents    True                 True                 5/8/2019 9:45 AM

NNow that we have a PowerShell session we can write a simple .ps1 script to create our Extended Events session.   For this example we will create a session similar to the one we created with the "New Session Wizard".  You can read through the comments in the complete script listing below and see that we basically have a command for each item we are adding to the session.  As with the other methods for creating a session, you can add as many events, predicate/filters, fields or targets as you require.

import-module sqlservercd SQLSERVER:
cd XEvent  
$h = hostname  
cd $h  
# set the default instance as location to create the XEvent session  
$store = dir | where {$_.DisplayName -ieq 'default'}
# name the instance
$session = new-object Microsoft.SqlServer.Management.XEvent.Session -argumentlist $store, "First_XEvent_Session_Powershell"  
# add the deadlock event
$event = $session.AddEvent("sqlserver.database_xml_deadlock_report")  
# add a global field
$event.AddAction("package0.callstack")  
# set a filter predicate
$event.PredicateExpression="([sqlserver].[database_name]=N'Production')"
# add a target
$session.AddTarget("package0.ring_buffer")  
# create the session
$session.Create()  
Additional Information

Last Update: 6/4/2019




More SQL Server Solutions











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.






download





get free sql tips

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