Home > powershell, sql > Splitting SQL Server Logs by ProcessInfo

Splitting SQL Server Logs by ProcessInfo

I wanted a way to parse through my SQL logs and be able to look at one particular spid or ProcessInfo class such as Server, Backup, Logon, etc. I came up with the following PowerShell script: *You must have either trace flag 1204 and/or 1222 turned on. (More info)

param($path)
if($path -eq $null) {exit}
if(Test-Path $path) {
    "Parsing $path"
    $spid = $null
    gc $path | %{
        #if ($_.Length -gt 35 -and $_.Substring(24, 12).StartsWith("spid")) {
        if($_ -match "^\d{4}") {
            $spid = $_.Substring(24, 12).Trim().PadRight(12, '_')
        } else {
            if($_.Trim().Length -lt 1 -and $spid -ne $null) {}
            else {
                $spid = "Default".PadRight(12, '_')
            }
        }
        $outfile = $path.Substring(0, $path.LastIndexOf(".")) + [string]::Format(".{0}.txt", $spid)
        $_ | Out-File -FilePath $outfile -Encoding "ASCII" -Append
    }
} else {"Bad path given"}

If you dump your log file via the following command, this works really well.
sqlcmd -S MyServer\Inst1 -Q "xp_ReadErrorLog" -o myserver.log

Once you have it, just call the script and specify the -Path parameter as such:
& '.\Split SQL Log by ProcessInfo.ps1' -Path C:\path\to\myserver.log

It looks at each line and figures either the spid or processinfo class and puts the line into a file with that string in its name. If it sees a blank line, it will put it into the previous file. WARNING: This could result any many files.

Yes, you could dump this to SQL or parse this some other way, but I wanted to split it into files so I can run it through another process to get all deadlock information that I’ll be posting later.

Download script

Categories: powershell, sql Tags:
  1. No comments yet.
  1. No trackbacks yet.

%d bloggers like this: