Searching the SQL Error Log with PowerShell

Another post in the PowerShell Box of Tricks series. Here is another script which I use to save me time and effort during my daily workload enabling me to spend more time on more important (to me) things!

Yesterday we looked at Reading Todays SQL Error Log Today we are going to search all* of the SQL Error Logs. This is usually used by DBAs to troubleshoot issues

The SQL Server Error Logs (by default) are located in the folder Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and are named as ERRORLOG.n files. The most recent has no extension the rest 1 to 6.

Using PowerShell you can easily find the location of the SQL Error Log using the ErrorLogPath Property

image

You can also read it with PowerShell using the ReadErrorLog Method. This has the following properties LogDate, Processinfo and Text. You can easily filter by any of those with a bit of PowerShell 🙂

I have created a function which takes two parameters $SearchTerm and $SQLServer adds *’s to the Search Term to allow wildcards and searches each of the SQL Error Logs

image

Simply call it like this and use the results as needed

image

Of course, as the results are an object you can then carry on and do other things with them

image

The code can be found here

 

* Technically we are only searching the default number of 7 but if your environment is different you can easily add the lines to the function

Advertisements