Powershell & Extended Events

I have been doing a lot of work with SQL Server lately. Over on www.sqlskills.com Jonathan Kehayias has written a series of very good blog posts regarding working with Extended Events. I could not however find very much information around Powershell and Extended Events. After some searching on MSDN and TechNet I was able to piece together how to make a stand alone script to create an extended event session. This link was invaluable: http://technet.microsoft.com/en-us/library/ff877887.aspx


#$Instance = "SQLALL\NOGISQL2012"
$InstServiceName = "NOGISQL2012"
#$NodeName = "SQLALL"

Function CreateXEventSession()
    Set-Location SQLSERVER:\XEvent
    $h = hostname
    cd $h
    $store = dir | where {$_.DisplayName -ieq $InstServiceName}
    $session = new-object Microsoft.SqlServer.Management.XEvent.Session -argumentlist $store, "TestSession"
    $event = $session.AddEvent("sqlserver.sql_statement_completed")

write-host "--<>------  start  ------<>--"
Import-Module -Name SQLPS
write-host "--<>------  end  ------<>--"
# todo --> start the session


good luck and have fun,


Skip to main content