# 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.