SharePoint Consulting Blog

Normally, we check SQL Server error log through SQL Server Management Studio or any text editor. By default, error log is located at ~\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ ERRORLOG.n1 (Here, n is instance name and n1 is nth number - dynamically appended). But, if you’re managing thousands or even just hundreds of SQL Server systems, you might spend most of the day manually checking each server.

So, what is better way to automate error log process and get error log results for each instance as an output to a single HTML file?

Solution:
One possible solution to achieve this is by using PowerShell script. Below is a PowerShell script to automate error log and generating a report for daily monitoring of SQL Server error logs. We need to add all SQL servers' instance name in one text file. This script will read all instances mentioned in text file one by one, and generate a error log in HTML file.

Prerequisites:
Module invokesqlquery needs to be installed. Invokesqlquery can be downloaded from http://powershell4sql.codeplex.com

Powershell script:

<#

This script displays the error log entries for all listed servers for the last one day.

The script can take a few minutes to run if the error logs are large and you are looking back over several days.

#>

cls # This command will clear the screen of PowerShell window.

import-module invokesqlquery #This will import the module named "invokesqlquery" which we have downloaded and placed in module folder

$today    = (get-date).toString() #This will store today's date in $today variable

$all      = @() #This is a declaration of an array type variable to store the output in $all variable

$lookback = ((get-date).adddays(-1)).ToString()  # This will store previous date in $lookback variable

$serversList = "{your serverlist's text file path}\serverlist.txt" #This will store a full path of text file in which you have listed down the sql server's name

foreach ($server in get-content $serversList | select-object @{Name = 'serverName'; Expression = {$_}})  # This foreach loop will read the text file from given path and execute the code for each server name in that text file

{

"$((get-date).toString()) - Checking SQL Error Logs on $($server.servername)..."   #This line is for just displaying the name of server on the screen for which the process of checking SQL Error Logs is running

try  {

<# Explanation of the below code :

invoke-sqlquery -query : This is a command which executes the sql query

master..xp_readerrorlog : This is a stored procedure of in sql server to get the logs. We have passed some parameters to filter the logs.

select-object : This command is for specifying the columns which should be shown in the output.    

where-object : This command will filter the output and display only those data which contains the specific word like "error" in the Text. Other records will be discarded and will not be there in the output.

` : This symbol is to concat the line

  #>

#invoke-sqlquery -query :

$all += invoke-sqlquery -query "EXEC master..xp_readerrorlog 0, 1, null, null, '$lookback', '$today' " -server $server.servername | select-object @{Name="Server"; Expression={$server.servername}}, LogDate, ProcessInfo, Text | `

where-object {$_.Text -match 'error'}| `

where-object {$_.Text -notmatch 'setting database option recovery to'}

}

catch {"Unable to read SQL error log from server $server"}}

$all | ConvertTo-Html | Out-File "{Full Path of the output}\HTMLReport.html" #This line will export the output in the html file at given path.