Use PowerShell to Parse SQL Server 2012 Error Logs
Summary: Microsoft Scripting Guy, Ed Wilson, shows how to use Windows PowerShell to parse SQL Server 2012 error logs.
Microsoft Scripting Guy, Ed Wilson, is here. The Scripting Wife and I have been in a number of email threads with Mark Schill, the leader of the Atlanta Windows PowerShell User Group, and the person in charge of putting together Windows PowerShell Saturday #003 in Atlanta (Alpharetta, Georgia) this Saturday, October 27, 2012. He has an Xbox to give away, as well as a truckload of swag from an amazing raft of sponsors.
Tickets are still available for this five-star event. I am making a couple of presentations, along with some other awesome speakers. With three tracks—beginner, intermediate, and advanced—there will be something for everyone. We will be talking about Active Directory, Exchange, SQL Server, Windows 8, Windows Server 2012 as well as basic and advanced fundamental Windows PowerShell—I am really looking forward to it.
Teresa is looking forward to the date for a different reason—it is the day she gets her new Microsoft Surface—she went ahead and preordered it to ensure she would be one of the first ones with the device. If all goes well, she will be demoing it for her friends and followers at Windows PowerShell Saturday in Alpharetta.
A while back, I wrote two Hey, Scripting Guy! Blog entries where I talked about working with the SQL Server error log. By using the SQL Server 2012 provider for Windows PowerShell, this task is much easier.
Note This is the fifth article in a series of articles discussing using Windows PowerShell to manage SQL Server 2012. You should read the first four articles in this series prior to reading this article. In the first article, I introduced the SQL Server 2012 provider for Windows PowerShell. In the second article, I talked about the properties and methods that become available once you store the instance in a variable. In the third article, I talked about using Windows PowerShell to work with database objects. The fourth article discussed backing up SQL Server database objects.
Querying the SQL Server 2012 error log with PowerShell
The server object, available from the SQL / Server name / Instance Name node on the Windows PowerShell SQL Server 2012 node has a method named ReadErrorLog. This method makes it easy to access the SQL Error Log. The following commands show storing the server object in the $server variable, and then call the ReadErrorLog method.
$server = Get-Item SQLSERVER:\sql\sql1\Instance_A\
$server.ReadErrorLog()
The following image shows the commands to read the log as well as the output from the method call.
In reality, there are 7 SQL Server error logs (numbered 0 – 6) and these logs automatically roll off with log 0 being the most current. There are only three properties exposed through the SQL error log. These properties are shown here.
[sql1]: PS C:\> $server.ReadErrorLog(0) | gm -mem property
TypeName: System.Data.DataRow
Name MemberType Definition
—- ———- ———-
LogDate Property datetime LogDate {get;set;}
ProcessInfo Property string ProcessInfo {get;set;}
Text Property string Text {get;set;}
Searching the SQL error logs
To search the most recent error log for information from the backup process, I might use a command such as the one shown here.
$server.ReadErrorLog() | where { $_.processInfo -eq ‘backup’}
The results from this query are shown here.
[sql1]: PS C:\> $server.ReadErrorLog() | ? { $_.processInfo -eq ‘backup’}
LogDate ProcessInfo Text
——- ———– —-
10/16/2012 6:03:00 PM Backup Database backed up. Data…
10/16/2012 6:03:00 PM Backup Database backed up. Data…
10/16/2012 6:03:00 PM Backup Database backed up. Data…
10/16/2012 6:03:01 PM Backup Database backed up. Data…
10/16/2012 6:08:01 PM Backup Log was backed up. Datab…
10/16/2012 6:15:39 PM Backup Log was backed up. Datab…
10/16/2012 6:15:39 PM Backup Log was backed up. Datab…
10/16/2012 6:15:39 PM Backup Log was backed up. Datab…
10/16/2012 6:15:39 PM Backup Error: 3041, Severity: 1…
10/16/2012 6:15:39 PM Backup BACKUP failed to complet…
Note Because I am using Windows PowerShell 3.0, I can use the simplified Where-Object syntax for this query. Here is the revision: $server.ReadErrorLog() | ? processInfo -eq ‘backup’
I see that one of my backup jobs ended with an error 3041; at the same time, another error message states that the backup failed to complete. I can gain more information about these two errors by selecting them individually. Because I know the processInfo is backup, I do not need to return that column. But before I get too busy filtering my output, I want to ensure I have just the two error records with which I need to work. My first attempt does not work, because I have my order messed up. The command and output are shown here.
[sql1]: PS C:\> $server.ReadErrorLog() | ? { $_.processInfo -eq ‘backup’ -AND $_.Text
-match ‘failed’ -OR $_.text -match ‘error’}
LogDate ProcessInfo Text
——- ———– —-
10/16/2012 4:43:43 PM Server Logging SQL Server messa…
10/16/2012 4:43:43 PM Server Registry startup paramet…
10/16/2012 6:15:39 PM Backup Error: 3041, Severity: 1…
10/16/2012 6:15:39 PM Backup BACKUP failed to complet…
I need to first find text that contains the word filed or the word error in the string. THEN, from that batch of error records, I need to find the processInfo that is equal to backup. The revised command is shown here.
[sql1]: PS C:\> $server.ReadErrorLog() | ? { $_.Text -match ‘failed’ -OR $_.text -mat
ch ‘error’ -AND $_.ProcessInfo -eq ‘backup’}
LogDate ProcessInfo Text
——- ———– —-
10/16/2012 6:15:39 PM Backup Error: 3041, Severity: 1…
10/16/2012 6:15:39 PM Backup BACKUP failed to complet…
I now revise my command to create a better output of the failure and the error. I use a Format-table cmdlet, and I autosize it as well as wrap the output. The command and output are shown here.
[sql1]: PS C:\> $server.ReadErrorLog() | ? { $_.Text -match ‘failed’ -OR $_.text -mat
ch ‘error’ -AND $_.ProcessInfo -eq ‘backup’} | ft logdate, text -auto -wrap
LogDate Text
——- —-
10/16/2012 6:15:39 PM Error: 3041, Severity: 16, State: 1.
10/16/2012 6:15:39 PM BACKUP failed to complete the command BACKUP LOG
ReportServer$INSTANCE_ATempDB. Check the backup application
log for detailed messages.
Okay. So now, I want to see how long this has been going on. To do this, I use the range operator to create a range of numbers from 0 to 6, which corresponds with the number of SQL error logs that I can keep on my system. I am now going to read each of these logs and store the results into a single variable. While I am at it, I will filter out only the backup logs. This code is shown here.
$logs = 0..6 | % {$server.ReadErrorLog($_) |? { $_.processInfo -eq ‘backup’} }
Now I want to filter out the errors and the failures from the backup jobs and sort the results by date. The command is shown here.
$logs | ? {$_.text -match ‘error’ -OR $_.text -match ‘failed’ } | sort logdate
The commands and the output from the commands are shown in the following image.
The last thing to do is to clean up the output so it is more readable for me. I add back the format-table I used earlier. Here is the command.
$logs | ? {$_.text -match ‘error’ -OR $_.text -match ‘failed’ } | sort logdate | ft logdate, text -auto –wrap
The command and associated output are shown here.
I can now see exactly which database is not backing up properly. In addition, I can see how long the problem has existed and the error generated.
Well, that’s about it for using the SQL Server 2012 provider for Windows PowerShell to review the SQL error log.
Join me tomorrow when I will have a guest article written by Brian Wilhite—one of the speakers at Windows PowerShell Saturday in Atlanta, GA (Alpharetta). He will be reprising the article he wrote about daylight savings time—a timely topic (if you will forgive the pun).
I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.
Ed Wilson, Microsoft Scripting Guy
Light
Dark
0 comments