stuck on older versions of sql server? check out our trace commands.

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:

 

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:

 

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:

 

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:

 

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:

 

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

4 thoughts on “stuck on older versions of sql server? check out our trace commands.

  1. George Dickson Reply

    This is very nice! This will save me from having to RDP into the server to read the trace file(s).

  2. Pollus Reply

    How would one define a trace with this? I see start, stop, remove, get but where’s new or add?

    • Chrissy LeMaire Post authorReply

      Hey Pollus,
      Defining it would only be via T-SQL. Similar to creating a new Extended Event, it was just too complicated.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.