Summary: Learn how to search SQL Server error logs to return useful information by using Windows PowerShell to do the heavy lifting.

  Hey, Scripting Guy! I need to query the SQL Server error log for specific errors. But I am not real good with Windows PowerShell. What is the best way to do this?

—LH

Hello LH,

Microsoft Scripting Guy, Ed Wilson, is here. For me, one of the coolest things about getting to go to TechEd, is all the people I meet. The Scripting Guys had a booth peopled by Mark Schill (president of the Atlanta PowerShell Users Group), the Scripting Wife, and me. This booth was a magnet for everyone who was even remotely connected with Windows PowerShell. We ended up running our own conference from the expo floor. We had autograph sessions with Don Jones and Jeffery Hicks, and even formal Q & A sessions with Don Jones as well.

One afternoon, we were really lucky because Chad Miller and Steven Murawski stopped by. These are two of the authors of the SQLPSX project on CodePlex. They appear in the following photo.

LH, you may wonder why I am talking about SQLPSX. The reason is because it provides the easiest access to the SQL Server error log. As I discussed in yesterday’s blog, there are many different ways one can access the SQL Server error log, but using SQLPSX is probably the easiest.

SQLPSX requires Server Management Objects (SMO) to be installed on your system. If you have the SQL Server Management Studio (SSMS), you have SMO installed on your machine. If you do not have SSMS installed, you can install it from the SQL Server 2008 R2 (or other version) disk. You can also download SMO from the Microsoft SQL Server 2008 Feature Pack.

There can be problems in getting the Windows PowerShell snap-ins to work if you only download the Windows PowerShell feature. In addition, you may find that there are common language runtime (CLR) and other types of dependencies. The simplest route is to install the SSMS. There is also a free version of the SSMS. It provides much of the same functionality as the one I installed from the SQL Server installation disk. One thing that is missing is that there is no GUI for SQL Server Agent jobs. This is because SQL Server Agent is not available in the SQL Server Express version. However, the SMO is the same in both versions.

If you install the SSMS on your system (for SQL Server 2008), you also get the SQL provider database and the Windows PowerShell cmdlets. The cmdlets are contained in two snap-ins. These two snap-ins load via the Add-PSSnapin Windows PowerShell cmdlet as shown here.

Add-PSSnapin SqlServerCmdletSnapin100

Add-PSSnapin SqlServerProviderSnapin100 

One problem with the PSSnapins (actually, there are several problems with the PSSnapins, and that is why the Windows PowerShell team has moved away from snap-ins and toward modules) is that they generate an error if you attempt to load them a second time. This error is shown here.

The command completes, but two errors are added to the $error object. To avoid errors when you load a PSSnapin in your script, you can use my Test-Snapin function. This function is shown here.

Test-Snapin function

function Test-Snapin

{

  <#

   .Synopsis

    Checks to see if a snapin is loaded. If not, it loads same.

   .Description

    This function checks to see if a snapin is loaded. If not, it loads same.

    It will also report if a snapin is not available.

   .Example

    Test-Snapin SqlServerCmdletSnapin100

    Tests to see if the SqlServerCmdletSnapin100 snapin is loaded. If not, it

    will load it if it exists. If it does not exist, it reports this fact, and

    exits the script.

   .Parameter Name

    The Name of the snapin to load

   .Notes

    NAME:  Test-Snapin

    AUTHOR: ed wilson, msft

    LASTEDIT: 05/26/2011 18:34:11

    KEYWORDS: Scripting Techniques

    HSG: HSG-6-1-11

   .Link

     Http://www.ScriptingGuys.com

 #Requires -Version 2.0

 #>

 Param ([string]$name)

 if(!(Get-PSSnapin |

     Where-Object { $_.name -eq $name }))

  {

    if(!(Get-PSSnapin -registered |

          Where-Object { $_.name -eq $name }))

      {

        “$name is not registered. Exiting script.”

        exit

     } #end if registered

   ELSE

     {

       add-psSnapin -name $name

     } #end else registered

  } #end if not get-pssnapin

 ELSE

  { “$name cmdlets already loaded” }

} #end Test-Snapin

The following CheckSnapinQueryPubs.ps1 script incorporates the Test-Snapin function and the previous LoadSnapInQueryPubs script. The script is significantly larger. However, if you put the Test-Snapin function into your startup profile or startup module, the function is always available, and you do not need to copy and paste it for each use.

CheckSnapinQueryPubs.ps1

function Test-Snapin

{

  <#

   .Synopsis

    Checks to see if a snapin is loaded. If not, it loads same.

   .Description

    This function checks to see if a snapin is loaded. If not, it loads same.

    It will also report if a snapin is not available.

   .Example

    Test-Snapin SqlServerCmdletSnapin100

    Tests to see if the SqlServerCmdletSnapin100 snapin is loaded. If not, it

    will load it if it exists. If it does not exist, it reports this fact, and

    exits the script.

   .Parameter Name

    The Name of the snapin to load

   .Notes

    NAME:  Test-Snapin

    AUTHOR: ed wilson, msft

    LASTEDIT: 05/26/2011 18:34:11

    KEYWORDS: Scripting Techniques

    HSG: HSG-6-1-11

   .Link

     Http://www.ScriptingGuys.com

 #Requires -Version 2.0

 #>

 Param ([string]$name)

 if(!(Get-PSSnapin |

     Where-Object { $_.name -eq $name }))

  {

    if(!(Get-PSSnapin -registered |

          Where-Object { $_.name -eq $name }))

      {

        “$name is not registered. Exiting script.”

        exit

     } #end if registered

   ELSE

     {

       add-psSnapin -name $name

     } #end else registered

  } #end if not get-pssnapin

 ELSE

  { “$name cmdlets already loaded” }

} #end Test-Snapin

 

Test-Snapin SqlServerCmdletSnapin100

Test-Snapin SqlServerProviderSnapin100

Invoke-Sqlcmd -ServerInstance sql1 -Database pubs -Query “select * from Authors”

An interesting way to “deal with” detecting and loading the Windows PowerShell snap-ins for SQL Server is to create a Windows PowerShell module. Chad Miller discusses this approach in his blog.

If you have SSMS, you have these snap-ins available to you. After the snap-ins are loaded, you can use Invoke-SQLcmd to retrieve the SQL Server error log. The command to do this is shown here.

Invoke-Sqlcmd -ServerInstance “(local)” -Database master -Query xp_ReadErrorLog

Because of the “issue” with loading the Window PowerShell snap-ins for SQL Server, it might be simpler to use the Get-SQLData cmdlet from the SQLPSX. One nice thing about a module is that it does not generate an error if you attempt to load it multiple times.

I want to expand a bit on the previous example as well. The xp_ReadErrorLog extended stored procedure accepts four parameters. (These parameters are documented in Jay’s notes on SQL blog on MSDN). The first is the ERRORLOG number to retrieve. The default is 0, which is the most recent SQL Server error log. The second parameter is the log to review. The default log is the database engine (1), but the xp_ReadErrorLog extended stored procedure can also query the SQL Server Agent log (2). The third and fourth parameters are search strings.

Example commands:

The following command illustrates using this command with the Get-SQLData cmdlet from the SQLPSX to retrieve memory errors from the most recent SQL Server error log on the local SQL Server. (Note that I import only the sqlServer module from the SQLPSX because that module contained the functionality I needed. It is more efficient than importing all 13 modules.)

Import-Module sqlServer

get-sqlData -sqlserver “(local)” -dbname master `

   -qry “xp_ReadErrorLog 0, 1, ‘memory’, ‘sql server'”

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

The previous command is fine for a “one-off” type of command. However, if I need to execute the command more often, I need to take into consideration the way that SMO works. By default, SMO uses connection pooling; and therefore, it will persist connections. (By the way, this is the same way that SSMS works). In general, persisting connections is a good thing because it can reduce overhead and the associated load on the server.

However, when doing ad-hoc queries, SMO will create a new database object, and consequently a new connection. All of the opened connections will be closed when the Windows PowerShell console (or ISE) exits. However, if you leave the console open all day (like I do), this can grow to be a significant amount of overhead. The way to avoid this is to modify the code so that you create a database object, and then reuse that object. This technique is shown in the following code.

Import-Module sqlserver

$db = Get-SqlDatabase -sqlserver “(Local)” -dbname “master”

get-sqldata -dbname $db -qry “select * from sysdatabases

A better option might be to use straight ADO.NET. However, instead of hand coding a bunch of ADO.NET stuff (such as was examined yesterday), I can use the ADO module from SQLPSX.

Mike Shepard created the ADO module, and it has a lot of really cool functions. Use the Get-Help cmdlet to examine the functions that are exported by this module. Here is an example of using the ADO module.

Import-module adolib

invoke-query -sql “select * from sysdatabases” -server “(Local)” -database master

Another option would be to use the Invoke-SQLcmd2 function by Chad Miller. It is available in the Scripting Guys Script Repository. It behaves in a similar fashion to the Invoke-SQLCMD cmdlet, but it does not have any external dependencies. The function has comment-based Help; and therefore, you can use the Get-Help cmdlet to see syntax. After you download the code and load the function, you can use it as shown here.

Invoke-Sqlcmd2 -ServerInstance sql1 -Database pubs -Query ‘select * from authors’

If I am using the SQLPSX, however, I will use the Get-SqlErrorLog cmdlet. The advantage is that I can then pipe the results to the Where-Object cmdlet to search easily for information I need. An example of using the Where-Object to look for memory errors is shown in the following code.

Import-Module sqlServer

Get-SqlErrorLog -sqlserver “(local)” |

Where-Object { $_.text -like ‘*significant * memory *’}

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

LH, that is all there is to using Windows PowerShell to obtain and work with SQL Server error logs. Join me tomorrow when I will search the SQL Server error log for multiple 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