Read SQL Server Error Logs with Powershell and SMO

SQL Server troubleshooting and SQL Server problem analysis  usually results in a DBA reading the SQL Server Error Logs and other error logs , such as Windows Events Logs.

The extent of analysing error logs  depends on the problem. I’ve written in previous posts about the necessity to regularly review the SQL Server Error Logs. There is tons of detail or clues about other potential problems , which may not cause an outage today – but if left untreated could cause an outage tomorrow.

How to use xp_readerrorlog parameters to search SQL Error Logs ...

In a previous post , I wrote about generating a daily report on critical errors in SQL Server Error Logs. This can be generated once a day and emailed to the DBA team.

Powershell script to report SQL Error Log (SQL Server DBA)

The method used in these posts was to run a SQL statement on the SQL Server  - using Powershell as a framework to capture the details and email to the DBA team

There is an alternative method to extract errors from the SQL Server Error Logs. Using Powershell and SMO.

 SQL Server Management Objects (SMO) is a collection of objects designed for programming the  management of  Microsoft SQL Server. It is widely used in developing tools and other extensions for manipulating all aspects of SQL Server.

New  features are always being added to SMO - and particularly if building custom applications, gives the programmer a good level of control.

Here is an example of how to access SQL Server Error Logs using powershell SMO.

$myserever = New-Object Microsoft.SqlServer.Management.Smo.Server myserver\INST1 
$myserver.ReadErrorLog()

 A common error when attempting to instantiate the New-Object is this error message

New-Object : Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: make sure the assembly containing this type is loaded

A quick & dirty workaround is to start the script sequence by using:

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

Related articles

How to find the SQL Server Service Account

How to automate Sql Server Configuration Manager settings

dotNet framework 3.5 is a SQL Server 2012 prerequisite