Read SQL Server Error Logs with Powershell and SMO

01 May,2017 by Jack Vamvas

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")

 

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Read SQL Server Error Logs with Powershell and SMO


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer