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:

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$machineName = "UNIVAC"
$instanceName = "Production"
$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "$machineName\$instanceName"
$sqlServer.ReadErrorLog()

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.


Comments (2)

  1. ErrorLog Output to an text file says:

    Hello BuckWoody,

    The above powershell script gives me a result on the powershell window, i want the result in .txt file. How can i accomplish this.

    Please reply the solution to thilakraaj@gmail.com

  2. Eswar Reddy says:

    Great Script. I need to collect error-log file on multiple Server’s . I have developed below script which can be used for collecting the errors in error log on multiple server’s.

    import-module sqlps -DisableNameChecking
    $Servername= Get-Content D:\Powershell\PowerShellClass\PowerShellscriptsbyRamesh\Day-8\instancelist.txt
    $machine=hostname
    foreach($server in $Servername)
    {
    $sqlServer = new-object (“Microsoft.SqlServer.Management.Smo.Server”) “$Server”

    $sqlServer.ReadErrorLog()
    $sqlServer.ReadErrorLog() | Where {$_.Text -like “Error*”} | ConvertTo-Html | Out-File D:\Powershell\PowerShellClass\PowerShellscriptsbyRamesh\Day-8\$_-$date.html -Append

    }

    above script collecting the errors in error log in a single file . but i need to collect error log for each instance with separate file or html and file name could be in machine name and date (not single file) (ex “instancename-date.txt or html).. Hope you understand my requirement

Skip to main content