If you’re still using super old versions of SQL Server and don’t have access to awesome XEvents, we’ve got some commands to help simplify trace management.
Before I begin
Just a quick note, if you use newer versions of SQL Server and haven’t seen the XEvents Profiler in SSMS 17, it’s awesome! In my experience, it’s much faster than using Profiler and just as useful.
Sadly, however, it’s only available for SQL Server version 2012 and up.
On to traces
Traces are less exciting than Extended Events, but PowerShell makes them kinda fun to work with (at least for me). As with most of our commands, multiple servers are supported.
Check out the list of traces across my lab. This information was gathered in 324ms!
Note that SQL Server 2000 is not in the result set, as SQL 2000 does not support sys.traces 🙁
Get-DbaTrace
The above screenshot contains the results of Get-DbaTrace. This basically returns the results of select * from sys.traces. Here’s some sample usage:
# Get all traces on sql2017 Get-DbaTrace -SqlInstance sql2017 # Get the default trace on sql2016 Get-DbaTrace -SqlInstance sql2016 -Default # Get the traces with ID 2 on both sql2016 and sql2017 Get-DbaTrace -SqlInstance sql2016, sql2017 -Id 2
Read-DbaTraceFile
Next command is Read-DbaTraceFile, which is used to read the contents of the trace file. This basically returns the results of select * from [fn_trace_gettable]('$file', DEFAULT). Here’s some sample usage:
# Read every trace file on sql2014 Get-DbaTrace -SqlInstance sql2014 | Read-DbaTraceFile # Read the tracefile C:\traces\big.trc, stored on sql2016. Filter only results that have master or tempdb as the DatabaseName. Read-DbaTraceFile -SqlInstance sql2016 -Database master, tempdb -Path C:\traces\big.trc # Read the tracefile C:\traces\big.trc, stored on sql2016. Filter only results that have master or tempdb as the DatabaseName and that have 'EXEC SP_PROCOPTION' somewhere in the text. Read-DbaTraceFile -SqlInstance sql2016 -Database master, tempdb -Path C:\traces\big.trc -TextData 'EXEC SP_PROCOPTION' # Read the tracefile C:\traces\big.trc, stored on sql2016. Filter only results where LinkServerName = myls and StartTime is greater than '5/30/2017 4:27:52 PM'. Read-DbaTraceFile -SqlInstance sql2016 -Path C:\traces\big.trc -Where "LinkedServerName = 'myls' and StartTime > '5/30/2017 4:27:52 PM'"
Remove-DbaTrace
Next command is Remove-DbaTrace, which is used to stop and remove a trace. This command basically executes sp_trace_setstatus $traceid, 0 then sp_trace_setstatus $traceid, 2. Here’s some sample usage:
# To stop and remove all traces on sql2008 Remove-DbaTrace -SqlInstance sql2008 # To stop and remove the trace with id 2 on sql2008 Remove-DbaTrace -SqlInstance sql2008 -Id 2 # To stop and remove selected traces on sql2008 using Out-GridView Get-DbaTrace -SqlInstance sql2008 | Out-GridView -PassThru | Remove-DbaTrace
Start-DbaTrace
Next command is Start-DbaTrace, which is used to start one or more traces. This command basically executes sp_trace_setstatus $traceid, 1. Here’s some sample usage:
# To start all traces on sql2008 Start-DbaTrace -SqlInstance sql2008 # To start the trace with ID 2 on sql2008 Start-DbaTrace -SqlInstance sql2008 -Id 2 # To start selected traces on sql2008 using Out-GridView Get-DbaTrace -SqlInstance sql2008 | Out-GridView -PassThru | Start-DbaTrace
Stop-DbaTrace
Next command is Stop-DbaTrace, which is used to stop one or more traces. This command basically executes sp_trace_setstatus $traceid, 0. Here’s some sample usage:
# To stop all traces on sql2008 Stop-DbaTrace -SqlInstance sql2008 # To stop the trace with ID 2 on sql2008 Stop-DbaTrace -SqlInstance sql2008 -Id 2 # To stop selected traces on all servers within $serverlist using Out-GridView Get-DbaTrace -SqlInstance $serverlist | Out-GridView -PassThru | Stop-DbaTrace
PowerShell is awesome
These commands really highlight my favorite thing about managing SQL Server with PowerShell. First, you can manage multiple servers as easily as managing one, and the naming convention is straight forward and easy to remember.
Even after years of using traces, I always had to look up the syntax for sp_trace_setstatus or fn_trace_gettable. Now, though, it’s as easy as remembering Get, Start, Stop, Read and Remove 👍
- Chrissy
This is very nice! This will save me from having to RDP into the server to read the trace file(s).
This is awesome
How would one define a trace with this? I see start, stop, remove, get but where’s new or add?
Hey Pollus,
Defining it would only be via T-SQL. Similar to creating a new Extended Event, it was just too complicated.