Automate Trace Imports With PowerShell

By: Tim Smith | Comments | Related: > Profiler and Trace

Problem

We store thousands of trace files that we are looking to import into SQL Server for review and need a way to automate the import process. In some cases (or on some servers), we zip the trace files and need an automated way to unzip them, then import the trace files, and then remove the files.

Solution

While many environments still use SQL Server Traces, I highly advise to begin using Extended Events in SQL Server, because SQL Server Profiler (and the referenced function below) will be removed in a later edition of SQL Server (unfortunately, the version without it hasn't been announced yet, according to this). To learn more about Extended Events, MSSQLTips provides a great introductory tip. That being written, many 2005, 2008 and 2012 environments still use traces, so automating the import process will still be useful for these environments.

Importing SQL Server Trace Files Using PowerShell

In most cases, after we import a trace file, we remove it, especially in environments which zip their trace files. If we keep the file, the function will give us an option to specify a move path, where the trace file will be stored; otherwise, with no move path provided, it will remove the trace file after it imports it. We pass in the server ($server), database ($db), table ($table), and trace path ($tracepath), and the script takes care of (1) creating the table, (2) inserting all the trace file data, and (3) either removing the trace file, or moving it to an archive location. Note that the below function inserts the trace data using fn_trace_gettable.

Function Insert-TraceData($server, $db, $table, $tracepath, $move = $null)
{
    $trcs = Get-ChildItem $path -Filter *.trc
    $cnt = 1

    foreach ($trc in $trcs)
    {
        $scon = New-Object System.Data.SqlClient.SqlConnection
        $scon.ConnectionString = "SERVER=" + $server + ";DATABASE=" + $db + ";Integrated Security=true"
        $addtrace = New-Object System.Data.SqlClient.SqlCommand
        $addtrace.Connection = $scon

        $n = $trc.FullName
        if ($cnt -eq 1)
        {
            $addtrace.CommandText = "SELECT * INTO $table FROM ::fn_trace_gettable('$n', default)"
        }
        else
        {
            $addtrace.CommandText = "INSERT INTO $table SELECT * FROM ::fn_trace_gettable('$n', default)"
        }

        $scon.Open()
        $addtrace.ExecuteNonQuery() | Out-Null
        $scon.Close()
        $scon.Dispose()
    
        if ($move -ne $null)
        {
            Move-Item $n $move
        }
        else
        {
            Remove-Item $n
        }

        $cnt++
    }
}

PowerShell Script to Unzip and Import SQL Server Trace File

In some cases, environments will zip trace files and we can use the Unzip-Files function from the tip covering six useful PowerShell scripts to unzip the files, then call the import function (see the final two lines):

Function Unzip-Files ($fold)
{
    ## Note that we're not removing any zip files with this version of the function
 Add-Type -Path "C:\Windows\Microsoft.NET\assembly\GAC_MSIL\System.IO.Compression.FileSystem\v4.0_4.0.0.0__b77a5c561934e089\System.IO.Compression.FileSystem.dll"
    $a_zps = Get-ChildItem $fold -Filter *.zip
 
    foreach ($z in $a_zps)
    {
        [System.IO.Compression.ZipFile]::ExtractToDirectory($z.FullName, $fold)
    }
}

Function Insert-TraceData($server, $db, $table, $tracepath, $move = $null)
{
    $trcs = Get-ChildItem $path -Filter *.trc
    $cnt = 1

    foreach ($trc in $trcs)
    {
        $scon = New-Object System.Data.SqlClient.SqlConnection
        $scon.ConnectionString = "SERVER=" + $server + ";DATABASE=" + $db + ";Integrated Security=true"
        $addtrace = New-Object System.Data.SqlClient.SqlCommand
        $addtrace.Connection = $scon

        $n = $trc.FullName
        if ($cnt -eq 1)
        {
            $addtrace.CommandText = "SELECT * INTO $table FROM ::fn_trace_gettable('$n', default)"
        }
        else
        {
            $addtrace.CommandText = "INSERT INTO $table SELECT * FROM ::fn_trace_gettable('$n', default)"
        }

        $scon.Open()
        $addtrace.ExecuteNonQuery() | Out-Null
        $scon.Close()
        $scon.Dispose()
    
        if ($move -ne $null)
        {
            Move-Item $n $move
        }
        else
        {
            Remove-Item $n
        }

        $cnt++
    }
}

## Calling both functions
Unzip-Files -fold "C:\SavedTraces\"
Insert-TraceData -server "OURSERVER\OURINSTANCE" -db "Logging" -table "SavedTraceData" -tracepath "C:\SavedTraces\"

The above would unzip all the zip files with trace data, load the trace data into the SavedTraceData table on the Logging database, and remove the trace files after importing them, keeping the zip files. If I wanted to archive the trace files:

## Copy the functions from above, then call them with the below, which archives the trace files to "C:\SavedTraces\Archive\"
Unzip-Files -fold "C:\SavedTraces\"
Insert-TraceData -server "OURSERVER\OURINSTANCE" -db "Logging" -table "SavedTraceData" -tracepath "C:\SavedTraces\" -move "C:\SavedTraces\Archive\"

Depending on whether you use zip files or not and whether you remove the trace files post import or not, one of the above functions should save you a good amount of time and allow for easy automation. Remember that if your environment will continue upgrading SQL Server editions, start becoming familiar with Extended Events because support for SQL Server Trace will soon end.

Next Steps

If you don't zip trace files and want to import a multitude of them from a folder, use the first function.
If you zip trace files and don't want to keep the trace files, use the second set of code.
If you zip trace files and want to archive the trace files after import, use the third set of code.
Unless you don't intend to upgrade to a future SQL Server edition (meaning you're stopping where you are), start becoming familiar with Extended Events, as MSSQLTips provides some tips covering this topic.

About the author

Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips