Art of the DBA Rotating Header Image

January, 2016:

Managing SQL Error Logs with #Powershell

Whenever a problem arises, the first question I will ask is “what’s the error message?” Error logs and messages are extremely helpful, even if they feel vague. Because of this, it is vital to know how to leverage the error logs in SQL Server whenever you are troubleshooting an issue. The challenge is managing these logs efficiently and parsing them in a effective way. Powershell can offer some useful tools for reading and manipulating your logs, especially if you want to collect information across multiple SQL Server instances.

I’m not the first person to write about these methods. Buck Woody(@BuckWoodyMSFT) covers the basic method well and Allen White(@SqlRunr) has his own post that goes into more detail. I want to provide a little more information and expand on how it can be useful to you.

Getting Started

Reading SQL Server error logs is pretty simple, but requires us to make use of the SMO objects and the .ReadErrorLog() method to get the data from the log. Let’s start by simply reading the SQL Server error log and see what the output looks like (I’ll be using the SQL Server provider for this):

(Get-Item SQLSERVER:\SQL\localhost\DEFAULT).ReadErrorLog()

 

1-23-2016 10-46-22 AM

Nothing too surprising here, so let’s dig a little deeper. As I reiterate whenever I talk to folks about Powershell, everything is a .Net object. With this in mind, let’s see what we’re dealing with:

$log = (Get-Item SQLSERVER:\SQL\localhost\DEFAULT).ReadErrorLog()
$log | Get-Member

 

1-23-2016 10-50-36 AM

Look at the output’s data type, which is a System.Data.DataRow object. This is useful to us because it is a strongly typed object, not just a collection of strings, giving us better access to the data within the log.  Now we can access the properties of the output and our data directly.

Notice the highlighted properties, which are the output columns. Let’s leverage these properties to filter our data:

$log | Where-Object {$_.Text -like '*Startup*'} | Sort-Object LogDate | Format-List Text

 

1-23-2016 11-06-13 AM

What’s going on here? First off, we take the object and filter out only rows where the Text column/property value is like ‘Startup’. Next we sort by the LogDate column/property to make sure the data displays oldest to newest. Finally, the output is formatted to show only the Text column/property. This is all possible because of the object model.

We could get more advanced with these patterns. Allen’s blog post shows some great techniques for filtering out error messages and DBCC calls. The key point to focus on here is the behavior of the output and how we can use objects to better parse our data. Understanding these patterns really open up the capabilities of Powershell and using it for reviewing your logs.

Beyond the Basics

A gotcha to be aware of with this method is that it will only read the current active log. Depending on the last time your service restarted or when you cycle your SQL error log, data you are looking for could be in an archived log. How do we load and read these? We can retrieve this information using an index value that corresponds to the older log file:

(Get-Item SQLSERVER:\SQL\localhost\DEFAULT).ReadErrorLog(0) | Select-Object -First 5
(Get-Item SQLSERVER:\SQL\localhost\DEFAULT).ReadErrorLog(1) | Select-Object -First 5

 

1-23-2016 11-19-20 AM

What if we want to collect all our error logs? Or filter all of our entries based on date, not which log they are in? This is actually easier than it sounds, it is just a matter of using methods. Specifically, we can find out how many archive logs there are by using .EnumErrorLogs(), which allows us to collate all of the logs into a single collection:

$logs = (Get-Item SQLSERVER:\SQL\localhost\DEFAULT).EnumErrorLogs()
$combinedlog = $logs | ForEach-Object {(Get-Item SQLSERVER:\SQL\localhost\DEFAULT).ReadErrorLog($_.ArchiveNo)} $combinedlog | Measure-Object -Property LogDate -Minimum -Maximum | Format-List Property,Count,Minimum,Maximum

 

1-23-2016 11-34-29 AM

Using the .EnumErrorLogs() method, we can get a collection of objects that represent all of the archived logs. From there, we just create a simple loop (using ForEach-Object) to read each error log and add the entries into a larger collection. Finally, we filter and manage this collection in the same way we would an individual error log.

Don’t Stop Now!

I started playing with this recently thanks to Josh Luedeman(@JoshLuedeman), who had asked me about reading the SQL Agent log. I knew, based on the earlier posts I linked, how to read the SQL Server log, but I was not sure if the same methods could be used for the Agent. The good news is….absolutely!

It turns out that the SQL Server Agent class, JobServer, supports all the same error log methods. This means that all of the patterns that we have covered can be applied to parse the Agent logs:

$agentlogs = (Get-Item SQLSERVER:\SQL\localhost\DEFAULT\JobServer).EnumErrorLogs()
$combinedagentlog = $agentlogs | ForEach-Object {(Get-Item SQLSERVER:\SQL\localhost\DEFAULT\).ReadErrorLog($_.ArchiveNo)}
$combinedagentlog | Sort-Object LogDate -Descending | Select-Object -First 10

 

1-26-2016 9-27-19 AM
This is great because it means we have access into some of the other error logs. We could even combine our log objects for one time parsing. There are lots of possibilities here. What you should consider is the nature of the problem you are solving or process you are automating. These patterns simply become additional tools at your disposal.

We have a lot of options for parsing objects in Powershell. The key is to understand how the object model behaves. This is what really sets Powershell apart from many other scripting languages: its object aware foundation. Understanding this will open up entire new levels of functionality for you and the tasks you want to script.