THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Buck Woody

Carpe Datum!

Open the SQL Server Error Log with PowerShell

Using the Server Management Objects (SMO) library, you don’t even need to have the SQL Server 2008 PowerShell Provider to read the SQL Server Error Logs – in fact, you can use regular old everyday PowerShell. Keep in mind you will need the SMO libraries – which can be installed separately or by installing the Client Tools from the SQL Server install media. You could search for errors, store a result as a variable, or act on the returned values in some other way.

Replace the Machine Name with your server and Instance Name with your instance, but leave the quotes, to make this work on your system:

$machineName = "UNIVAC"
$instanceName = "Production"
$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "$machineName\$instanceName"

Want to search for something specific, like the word “Error”? Replace the last line with this:

$sqlServer.ReadErrorLog() | where {$_.Text -like "Error*"}

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. Yes, there are always multiple ways to do things, and this script may not work in every situation, for everything. It’s just a script, people. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

Published Monday, March 29, 2010 7:35 AM by BuckWoody



AllenMWhite said:

You can also specify previously used error logs like this:

$sqlServer.ReadErrorLog(1)  -- gets you ErrorLog_1


March 30, 2010 6:41 PM

Ken said:

I got the following code to work on my Central Management Server (VMDEV-APP11):

Invoke-sqlcmd2                  `

 -ServerInstance "VMDEV-APP11" `

 -Database dba                 `

 -Query "

   select s.server_name

   from msdb.dbo.sysmanagement_shared_registered_servers s, msdb.dbo.sysmanagement_shared_server_groups g

   where s.server_group_id = g.server_group_id"  | `

foreach-object {

   # If the server name is a named instance, the path is complete, otherwise append "\default"

   If ($_.server_name -like "*\*")

     {$server_path = $_.server_name}


     {$server_path = $_.server_name + "\default"}

   $server = $_.server_name

   $logs = 0..6 | % { (get-item SQLSERVER:\sql\$server_path).ReadErrorLog($_) }

   Write-DataTable -ServerInstance "VMDEV-APP11" -Database "DBA" -TableName "SQLErrorLog_raw" -Data $logs

 } # End foreach-object processing of registered servers

The problem is the security - or lack of security - for the account I use to run the code.  In order to get this to work, I had to add the account that runs the script to both the local Administrators group on the remote host and the SQL sysadmin role.  My security officer is not happy.  Any idea what the minimum privileges are to get "get-item SQLSERVER:\sql\$server_path).ReadErrorLog($_)" to work?


September 25, 2015 10:57 AM
New Comments to this post are disabled

About BuckWoody

This Blog


Privacy Statement