PROBLEM: You need an easy way to review the MS SQL Server logs of all the servers you manage.
SOLUTION: Use the Get-SqlErrorLog function from the SQL Server PowerShell Extensions (SQLPSX).
I will show you some of the things you can achieve with the use of the Get-SqlErrorLog function and a bit of creativity. We will start by retrieving the content of the current error log of our server “TUIRA” by executing:
Get-SQLErrorLog –SQLServer "TUIRA"
In the next example we want to retrieve the content of log file number 4 by specifying the optional parameter lognumber of the Get-SqlErrorLog function.
Get-SQLErrorLog –SQLServer "TUIRA" –lognumber 3
Now, getting the entire error log of a SQL server is not very useful, specially from a busy server. When I’m looking into the error logs I do so with a purpose, most of the time is because I’m looking for errors or something specific. In this following example, we’ll apply a filter and display entries where the word “error” exist in the message.
[string] $sSQLServerName = "TUIRA"
Get-SQLErrorLog –SQLServer $sSQLServerName|WHERE {$_.Text `
–match "error"}|Format-Table -AutoSize
If you get in the habit of checking these logs on a daily basis, you can filter your result a bit further by just retrieving the logs starting from the previous day. Here is an example of how to accomplish that.
[string] $sSQLServerName = "TUIRA"
$a = Get-Date
$d = $a.day – 1
$m = $a.month
$y = $a.year
$dDate = "$m/$d/$y"
Get-SQLErrorLog –SQLServer $sSQLServerName|WHERE {$_.Text `
–match "error" -and $_.LogDate –ge $dDate}|Format-Table -AutoSize
And our last example includes an array with a list of SQL server names. We’ll loop through this array and retrieve error entries from their log files.
$a = Get-Date
$d = $a.day – 1
$m = $a.month
$y = $a.year
$dDate = "$m/$d/$y"
$aSQLServers = "SQLServer1","SQLServer2","SQLServer3","SQLServer4"
foreach ($sSQLServer in $aSQLServers) {
$sSQLServer
Get-SQLErrorLog –SQLServer $sSQLServer|WHERE {$_.Text `
-match "error" -and $_.LogDate –ge $dDate}|Format-Table `
–AutoSize}
As a DBA, part of my morning routine is to review error logs from the SQL servers I manage. In my production environment, I have a job that retrieves the list of production servers from an inventory database (iSQLPS) and generate an HTML report for each server. So by the time I get to my desk with my first cup of java for the day, I have the reports waiting.
Do not forget to download the SQL Server PowerShell Extensions from Codeplex.com
