It’s a real pain trying to get data out of the SQL Error Logs I’ve noticed. You either don’t cycle your files very often and end up with stupid amounts of data in there to sift through or you cycle them frequently and then you end up having to try and figure out which of the files is related to the data you want. You could use T-SQL to import all the files and grab stuff, alternatively you can use PowerShell to get information.
I wanted to take this process a step further so I created a PowerShell script that will accept servername, start and end date/time parameters and return to you, the kind reader, all the entries in the logs between those times (and just between those times). This is great for when someone asks you to give them information from the log for 3-7 days ago. One quick execution of the script and the data is sitting there in PowerShell grid format, ready to go. Even better, using the grid you can filter the information further and quickly copy/paste it to excel or some other program.
I’ve pasted the code down below, but it might be easier to download Get-SQLErrorLogs.ps1
This requires the PowerShell SQL snappins that come with 2008/R2 but can query versions 2000 and up.
Next steps are to add filtering and options to output to file. I’m sure I’ll get to that eventually.
<#
.SYNOPSIS
Retrieves SQL Server error log entries
.DESCRIPTION
Pulls error log details from SQL Server and outputs them to the client
.PARAMETER <paramName>
-SQLInstance (required) The SQL Instance to pull the logs from
-MaxDate (required) The most recent log record to return
-MinDate (required) the oldest log record to return
.EXAMPLE
./Get-SQLErrorLogs -SQLInstance MyInstance -MinDate 2012-05-10 -MaxDate "2012-05-15 09:00"
#>
param
(
[string]$SQLInstance = $(Read-Host -prompt "SQL Instance [e.g. MyClusterInstance]"),
[string] $MinDate = $(Read-Host -prompt "Lowest log date entry (yyyy-mm-dd hh:mm)"),
[string] $MaxDate = $(Read-Host -prompt "Highest log date entry (yyyy-mm-dd hh:mm"),
[int] $LogNumber
)
cls
$CheckDateMin = [datetime]::Parse($MinDate)
$CheckDateMax = [datetime]::Parse($MaxDate)
Function Main {
#Load the SQL snapins
Load-SQLSnapins
#Create a datatable to hold the data
$Results = New-Object system.Data.DataTable "Results"
#Setup columns
$LogDate = New-Object system.Data.DataColumn LogDate,([DateTime])
$ProcessInfo = New-Object system.Data.DataColumn ProcessInfo,([string])
$Text = New-Object system.Data.DataColumn Text,([string])
#Add columns to datatable
$Results.columns.add($LogDate)
$Results.columns.add($ProcessInfo)
$Results.columns.add($Text)
#Another to hold logs read
$ReadLogs = New-Object system.Data.DataTable "ReadLogs"
$ReadLogNum = New-Object system.Data.DataColumn LogNumber,([int])
$ReadLogs.columns.add($ReadLogNum)
#Shred the SQL Instance to get the right data
$SQLName = Shred-SQLName $SQLInstance
$InstanceName = Shred-SQLInstance $SQLInstance
#Grabs SQL Logs by Date
Get-SQLLogByDate $SQLName $InstanceName $SQLInstance $CheckDateMin $CheckDateMax
$results | Sort-Object LogDate | ogv
}
Function Shred-SQLInstance ($SQLInstance) {
$SQLInstance = $SQLInstance.ToUpper()
if ($SQLInstance -ilike "**")
{
$string = $SQLInstance.Split("")
$Instance = $string[1]
Return $Instance
}
else
{
$Instance = "DEFAULT"
Return $Instance
}
}
Function Shred-SQLName ($SQLInstance) {
$SQLInstance = $SQLInstance.ToUpper()
if ($SQLInstance -ilike "**")
{
$string = $SQLInstance.Split("")
$Name = $string[0]
Return $Name
}
else
{
$Name = $SQLInstance
Return $Name
}
}
Function Get-SQLLogByDate ($SQLName, $InstanceName, $SQLInstance,$CheckDateMin ,$CheckDateMax) {
IF ($InstanceName -eq "DEFAULT") {$Logs = DIR SQLSERVER:SQL$SQLName | Where-Object {$_.Name -eq $SQLInstance}; }
ELSE {$Logs = DIR SQLSERVER:SQL$SQLName | Where-Object {$_.Name -eq $SQLInstance}; }
$LogCount = ($Logs.EnumErrorLogs() | Measure-Object).Count
Write-Output "--------------------------------------------------------------------------------------------------------------"
Write-Output "Checking error logs on server $SQLInstance for logs created between $CheckDateMin & $CheckDateMax"
Write-Output "--------------------------------------------------------------------------------------------------------------"
Write-Output "Checking through $LogCount logs, please wait."
Write-Output "-----------------------------------------------"
$LogList = $Logs.EnumErrorLogs()
#we'll need to do something hokey here. SQL error log creation is not when the log was created, rather when it was closed
#not intended circumstances. This means we'll have to get the min log and then get the one before that
ForEach ($ErrorLog in $LogList) {
IF ($ErrorLog.CreateDate -ge $CheckDateMin -and $ErrorLog.CreateDate -le $CheckDateMax)
{
$CurrentLog = $ErrorLog.CreateDate
$CurrentLogName = $ErrorLog.Name
Write-Output "Currently reading error log $CurrentLogName dated $CurrentLog..."
#write the log number to the $ReadLogs datatable
$NewLogRow = $ReadLogs.NewRow(); $NewLogRow.LogNumber = $CurrentLogName; $ReadLogs.Rows.Add($NewLogRow);
$Logs.ReadErrorLog(($ErrorLog.Name)) | Where-Object {$_.LogDate -ge $CheckDateMin -AND $_.LogDate -le $CheckDateMax} |
% {$NewRow = $Results.NewRow();
$NewRow.LogDate = $_.LogDate;
$NewRow.ProcessInfo = $_.ProcessInfo;
$NewRow.Text = $_.Text;
$Results.Rows.Add($NewRow);
}
}
}
#Now we've read all the logs read the one before the newest so we get that extra data
#unless the most current log (0) is included in the results already
#Write-Output $ReadLogs | ft -AutoSize
$LowestLog = ($ReadLogs | Measure-Object LogNumber -Minimum).Minimum
IF ($LowestLog -gt 0)
{
$LogToRead = $LowestLog - 1
Write-Output "Currently reading error log $LogToRead..."
#write the log number to the $ReadLogs datatable
$NewLogRow = $ReadLogs.NewRow(); $NewLogRow.LogNumber = $CurrentLogName; $ReadLogs.Rows.Add($NewLogRow);
$Logs.ReadErrorLog($LogToRead) | Where-Object {$_.LogDate -ge $CheckDateMin -AND $_.LogDate -le $CheckDateMax} |
% {$NewRow = $Results.NewRow();
$NewRow.LogDate = $_.LogDate;
$NewRow.ProcessInfo = $_.ProcessInfo;
$NewRow.Text = $_.Text;
$Results.Rows.Add($NewRow);
}
}
#on the off chance that the dates reside in the current log only pull that one in
#but only if no other logs have been loaded (edge case)
IF (!$LowestLog)
{
Write-Output "Currently reading error log 0..."
$Logs.ReadErrorLog(0) | Where-Object {$_.LogDate -ge $CheckDateMin -AND $_.LogDate -le $CheckDateMax} |
% {$NewRow = $Results.NewRow();
$NewRow.LogDate = $_.LogDate;
$NewRow.ProcessInfo = $_.ProcessInfo;
$NewRow.Text = $_.Text;
$Results.Rows.Add($NewRow);
}
}
}
Function Load-SQLSnapins
{ $CheckSQLSnapins = get-pssnapin | where {$_.Name -match "SqlServerCmdletSnapin100"}
if (!$CheckSQLSnapins)
{
Add-PSSnapin SqlServerCmdletSnapin100
Write-Host "SqlServerCmdletSnapin100 Loaded"
}
else { Write-Host "SqlServerCmdletSnapin100 Already Loaded" }
$CheckSQLSnapins = get-pssnapin | where {$_.Name -match "SqlServerProviderSnapin100"}
if (!$CheckSQLSnapins)
{
Add-PSSnapin SqlServerProviderSnapin100
Write-Host "SqlServerProviderSnapin100 Loaded"
}
else { Write-Host "SqlServerProviderSnapin100 Already Loaded" }
}
main
SirSQL.net 