Use PowerShell to Get the SQL Server Error Log

ScriptingGuy1

 Summary: Microsoft Scripting Guy, Ed Wilson, talks about different ways to use Windows PowerShell to get the SQL Server error log.

Hey, Scripting Guy! I have recently inherited a Microsoft SQL Server 2008 R2 box, and I am concerned about the thing. I am not a DBA, and to be honest the server sort of frightens me a bit. When I look into the Application log, there is lots of information about SQL, but it all seems to be SPAM, and it does not really seem to tell me much useful information. I am wondering if Windows PowerShell can help me with this server, and if so, where is the real error log for the SQL server?

—PV

Hello PV,

Microsoft Scripting Guy, Ed Wilson, is here. As it turns out, a couple of weeks ago I was talking about Windows PowerShell at the SQL Rally in Orlando, Florida. Microsoft SQL Server MVP, Aaron Nelson (aka SQLVariant), The Scripting Wife, and I hosted a Birds of a Feather table one afternoon, and we had the chance to talk to lots of SQL DBAs. The following picture was taken shortly before the Birds of a Feather session commenced.

Anyway, as it turns out, I had a rather interesting conversation with Microsoft SQL Server MVP, Allen Kinsel, about using Windows PowerShell to query the SQL Server error log. He stated that Windows PowerShell is too hard for non-developers to use. In some respects, he is correct. In other respects, it all depends on what example you are attempting to follow. With Windows PowerShell, there are many ways to accomplish the same task. Finding the easiest approach to a solution can often be a lot of work.

With this as background, PV, let us dive into your question.

By default, the SQL Server error log resides in the log directory under MSSQL as shown in the following image.

If I am not certain where the SQL Server error log resides, I use my Get-ErrorLogPath function to retrieve the path to the error log. This function is shown here (thanks to Chad Miller for the Add-Type code).

Note   Keep in mind, that I prefer using Add-Type instead of using the obsolete LoadWithPartialName static method from the Reflection.Assembly class. However, Add-Type attempts to load version 9 of the Microsoft.SqlServer.Smo, and that fails on my SQL Server 2008 R2 system. Therefore, it is necessary to use the strong name to attempt to load the version 10 of the SQL Management Objects (SMO). If this fails, then I fall back to using Add-Type to load the previous version.

Get-ErrorLogPath function

Function Get-ErrorLogPath

{

  <#

   .Synopsis

    Returns the path to the SQL Error Log

   .Description

    This function returns the path to the SQL Error Log

   .Example

    Get-ErrorLogPath

    Returns the path to the SQL Error Log on default instance of SQL on

    local machine

   .Example

    Get-ErrorLogPath -SQLServer SQL1

    Returns the path to the SQL Error Log on default instance of SQL on

    a SQL server named SQL1

   .Parameter SQLServer

    The name and instance of SQL Server

   .Notes

    NAME:  Get-ErrorLogPath

    AUTHOR: ed wilson, msft

    LASTEDIT: 05/27/2011 11:34:59

    KEYWORDS: Databases, SQL Server, Add-Type

    HSG: HSG-5-31-11

   .Link

     Http://www.ScriptingGuys.com

 #Requires -Version 2.0

 #>

 Param([string]$SQLServer = “(local)”)

 try {

    add-type -AssemblyName “Microsoft.SqlServer.Smo,

    Version=10.0.0.0,

    Culture=neutral,

    PublicKeyToken=89845dcd8080cc91″ -EA Stop }

 

 catch {add-type -AssemblyName “Microsoft.SqlServer.Smo”}

 $server = new-object (“Microsoft.SqlServer.Management.Smo.Server”) $SQLServer

 $server.ErrorLogPath

} #end function Get-ErrorLogPath

The complete function is uploaded to the Scripting Guys Script Repository, and it can easily be downloaded from there (it will help you avoid cut and paste errors and extraneous HTML stuff that can happen when attempting to copy code from the blog).

Also by default, the most recent error log is named ERRORLOG and it has no extension. Six archive copies of the error log are maintained. These files have an extension of 1 – 6. The error log is a plain text file. Unfortunately, it is not formatted as a CSV file, or even as a TSV, although the three columns are separated by spaces (just a variable amount of space). A sample ERRORLOG is shown in the image that follows.

At first, I played around with using the Import-CSV cmdlet to import the SQL Server error log directly, but the results were not great and it began to become really complicated rather quickly. This is because I needed to skip several rows from the top of the file, and because of the variable spacing between the rows in the remainder of the file. I thought to myself, “Dude, there has got to be an easier way to do this.”

I remembered that there is a SQL command called xp_ReadErrorLog, and I found a pretty cool script written by Buck Woody on his blog that uses this extended stored procedure. His script uses the .NET Framework SQLConnection class. The script is shown here.

ReadSqlErrorLogWithDotNetClasses.ps1

# Connect and run a command using SQL Native Client, Returns a recordset

# Create and open a database connection

$sqlConnection = new-object System.Data.SqlClient.SqlConnection `

   “server=(local);database=master;Integrated Security=sspi”

$sqlConnection.Open()

#Create a command object

$sqlCommand = $sqlConnection.CreateCommand()

$sqlCommand.CommandText = “xp_ReadErrorLog”

#Execute the Command

$sqlReader = $sqlCommand.ExecuteReader()

#Parse the records, read all three columns returned

while ($sqlReader.Read()) { $sqlReader[0], $sqlReader[1], $sqlReader[2] }

# Close the database connection

$sqlConnection.Close()

The ReadSqlErrorLogWithDotNetClasses.ps1 script is very useful. It returns the SQL Server error log information, and it is basically “ready to go,” meaning that I do not need to write the code. This is always an advantage. However, the code itself it a bit confusing for someone who is not real familiar with .NET Framework programming and ADO.NET in particular. When I was getting my MCDBA, I was hammered with ADO code on the exams, so I personally do not have a problem with the code. But I also remember studying for those exams, and it was a bit confusing to learn all that stuff.

Chad Miller decided to clean up the code a bit, so that the script is a bit more “PowerShell like.” In addition, he replaced the SQL Reader with a “plain old DataTable.” The revised code is shown here.

ReadSqlErrorLogWithDotNetClasses_PartDeux.ps1

$ServerInstance = “(Local)”

$conn=new-object System.Data.SqlClient.SQLConnection `

 $(“Server={0};Database=master;Integrated Security=True” -f $ServerInstance)

$conn.Open()

$cmd=new-object system.Data.SqlClient.SqlCommand(“xp_ReadErrorLog”,$conn)

$ds=New-Object system.Data.DataSet

$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)

$da.fill($ds) | out-null

$conn.Close()

$ds.Tables

There could be some other issues involved in the previous two examples. For one thing, the xp_ReadErrorLog extended stored procedure is not really very well documented, and I personally have had a few unpleasant surprises in the past when using undocumented features. One reason the product group does not document certain features is because they do not want to support those features. While they may work perfectly fine right now, any hotfix, service pack, or version upgrade could cause those undocumented features to quit working. But, hey, if it works right now, and it is easy to do, then I do not really have a major problem. I will worry about future upgrades when the future comes.

Of course, if one wants to use xp_ReadErrorLog, one can easily use other methods to invoke it so that it does not involve using ADO. (I will talk about that tomorrow.)

I also figured that I could use SQL Management Objects (SMO) to get information about the SQL Server error log, and sure enough, Chad Miller added a note to Buck’s article where he helpfully posted sample code to accomplish this task.

The Server class from the Microsoft.SQLServer.SMO .NET Framework namespace is quite extensive, and it is well documented on MSDN.

SMOSQLErrorLog.ps1

 try {

    add-type -AssemblyName “Microsoft.SqlServer.Smo,

    Version=10.0.0.0,

    Culture=neutral,

    PublicKeyToken=89845dcd8080cc91″ -EA Stop }

 

 catch {add-type -AssemblyName “Microsoft.SqlServer.Smo”}

 

$server = new-object (“Microsoft.SqlServer.Management.Smo.Server”) ‘(local)’

 

$server.ReadErrorLog(0)

Buck Woody even added a note to his blog post that states you can even use the SQL provider to access the SQL Server error log. This is shown here.

$MyServer = get-item SQLSERVER:\SQL\BWOODY1\SQL2K8

 $MyServer.ReadErrorLog(0)

If you have installed the SQLPSX modules (a free download from CodePlex) on your computer you can use the Get-SqlErrorLog cmdlet. This is shown here.

Import-Module sqlpsx

Get-SqlErrorLog -sqlserver “(local)”

The command and associated output are shown in the following image.

Generally, if I am not using some of the “advanced” features of the SQLPSX, then I will load only the SQLServer module. This is more efficient than loading all the other modules associated with SQLPSX. The revised command to do this is shown here.

Import-Module sqlserver

Get-SqlErrorLog -sqlserver “(local)”

PV, that should be enough different ways to access the SQL Server error log to confuse you, or hopefully to aid you in deciding how to best access the SQL Server error log. Tomorrow, I will talk about searching the SQL Server error log for specific errors.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy

ScriptingGuy1

Follow