SQL Server – Use Powershell to find what trace flags are running

Bit of a quick post this morning, continuing the theme of using powershell to standardise your SQL Server estate.  This one is on using PS to collect data on which global trace flags are running on your servers.

This post is not going to talk about which trace flag you should or shouldn’t set; there’s a pretty useful reference sheet on Brent Ozar’s site that goes through a load of trace flags and what they do.

So that you can assess your estate against this list, you can use the native T-SQL command to find out what trace flags are running: DBCC TRACESTATUS.  This is fine for a quick check on a server you’re connected to.

However, very few sites these days have one or two SQL Servers; and connecting to each and running this manually is tedious.  Instead, Powershell provides exactly the same functionality, but allows easier connection to lots of different servers and collating that information together.

The command to get the same input is very simple, you just need to create an object for your server using the smo, and then use the “EnumActiveGlobalTraceFlags” method to return the status of the running trace flags on that server:-

$SQLServer = new-object ("Microsoft.SQLServer.Management.Smo.Server") "MySQLServer"

$SQLServer.EnumActiveGlobalTraceFlags()

The output is shown below:-

TraceFlags

I’m going to put this together into a function and export it as a module, so that I can then add it to my profile and be able to run it in a loop from a list of servers that I keep in a table:-

function Get-SQLServerGlobalTraceFlags
{
    [cmdletbinding()]
    param([string]$Server)      

    #create an smo object for the SQL Server
    $SQLServer = new-object ("Microsoft.SQLServer.Management.Smo.Server") $Server

    #get the trace flag status
    $TraceFlags = $SQLServer.EnumActiveGlobalTraceFlags()

    #loop through the trace flags and add the servername in order to create an object with all the required rows to import into a table later
    ForEach($TraceFlag in $TraceFlags)
    {
        $data = @{"ServerName" = $Server
                ; "TraceFlag" = $TraceFlag.TraceFlag
                ; "Status" = $TraceFlag.Status}

        $Output = new-object psobject -Property $data
        write-output $output
    }

}

export-modulemember -Function Get-SQLServerGlobalTraceFlags

I can now get the list of servers from my inventory, and loop through them to use Get-SQLServerGlobalTraceFlags to return the global trace flag settings for each server into a PS object for further use, such as importing into a table using the Out-Datatable cmdlet and so on.

Advertisements

One thought on “SQL Server – Use Powershell to find what trace flags are running

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s