SQL Server Code-Named “Denali” - Viewing SQL Error Logs from WMI/SSMS when SQL Server is offline

John Emmanuel Mandam (johnema@microsoft.com) is a colleague of mine in the SQL Server Manageability team and he wrote up this useful blog on using WMI to query SQL Server Error Log Events in SQL Server code named “Denali”.

Query SQL Server ErrorLog Events using WMI (WQL)

To diagnose why SQL Server instance or instances are down is possible however it gets relatively harder if you wanted quickly get to event log data without performing multiple hoops from Registry to each instance Log folder to another and further from one error log file to another. Query like searches would help in these cases especially queries that will determine what is the cause of a specific SQL Server instance or instances not starting or completely offline; How can we provide specific search criteria against log events from SQL Server Instances; Or is there a way to query errors across multiple SQL Instances on the same machine that occurred in last few days;

Starting with SQL Server 11 CTPs, queries similar to these are now possible due to the two new WMI classes, namely SqlErrorLogFile and SqlErrorLogEvent that are added to management WMI provider (root\Microsoft\SqlServer\ComputerManagement11) to enable queries of this kind. Here is a brief description of these classes and PowerShell scripts that show how to query for log files and log events in these files.

Class SqlErrorLogFile

Enumerate SQL Server Error log files for a given instance. InstanceName and Name properties form the identity keys to each row returned by this class. By default the SqlErrorLogFile class returns Error Log files for a default instance.

From https://msdn.microsoft.com/en-us/library/ff963576(SQL.110).aspx

class SqlErrorLogFile

{

  [key,read : ToInstance] string InstanceName;

  [key,read : ToInstance] string Name;

  [read] uint32 ArchiveNumber;

  [read] datetime LastModified;

  [read] uint32 LogFileSize;

};

Class SqlErrorLogEvent

This class supports enumerating each SQL Server Error log file data in sequential order as recorded in Error Log files. InstanceName and LogDate fields are the identity keys for each row returned by this class.

From https://msdn.microsoft.com/en-us/library/ff963582(v=SQL.110).aspx

class SqlErrorLogEvent

{

  [key,read : ToInstance] string InstanceName;

  [key,read : ToInstance] datetime LogDate;

  [read] string FileName;

  [read] string ProcessInfo;

  [read] string Message;

};

Using these classes in PowerShell

Any valid WQLs that are similar to the following can be issued against these classes. For example, the following query shows all events that contain error from two test1 and default instances after a certain date:

SELECT * FROM SqlErrorLogEvent where (Instancename = ‘test1’ OR Instancename = 'MSSQLServer') AND FileName = ‘ErrorLog’ AND Message LIKE '%Error: %’ AND LogDate > '10/20/2010'

And here’s an example of a PowerShell script that uses this query:

#------------------Get-SqlErrorLogFiles------------------------------------------------

#Enumerate Error log files for a SQL Server instance

Function Get-SqlErrorLogFiles($instanceName)

{

    [System.Console]::WriteLine("WQL: Select * from SqlErrorLogFile Where InstanceName = `"$instanceName`"")

    $query = New-Object System.Management.ObjectQuery "Select * from SqlErrorLogFile Where InstanceName = `"$instanceName`""

    $scope = New-Object System.Management.ManagementScope "root\Microsoft\SqlServer\ComputerManagement11"

    $searcher = New-Object System.Management.ManagementObjectSearcher $scope,$query

    $resultCollection = $searcher.Get()

    DisplaySqlErrorLogFileContent($resultCollection)

}

#Display enumerated error log files

Function DisplaySqlErrorLogFileContent($objCollection)

{

    foreach ($sqlErrorLogFileObj in $objCollection)

    {

        [System.Console]::WriteLine("Name :" + $sqlErrorLogFileObj["Name"])

        [System.Console]::WriteLine(" InstanceName :" + $sqlErrorLogFileObj["InstanceName"])

        [System.Console]::WriteLine(" LastModified :" + [System.Management.ManagementDateTimeConverter]::ToDateTime($sqlErrorLogFileObj["LastModified"]))

        [System.Console]::WriteLine(" LogFileSize :" + $sqlErrorLogFileObj["LogFileSize"])

        [System.Console]::WriteLine(" Archive No :" + $sqlErrorLogFileObj["ArchiveNumber"]);

        [System.Console]::WriteLine(" ");

    }

}

if ($args -ne $null)

{

    $instance = $args[0]

}

else

{

    $instance = "MSSQLServer";

}

SqlErrorLogFiles $instance

#------------------------------end of Get-SqlErrorLogFiles.ps1-------------------------------------

#------------------Get-SqlErrorLogEvents.ps1---------------------------------

param($first, $second)

#Get all error events recorded in error log file

Function Get-SqlErrorLogEvents($instanceName, $fileName)

{

[System.Console]::WriteLine("InstanceName: {0}", $instanceName)

[System.Console]::WriteLine("FileName: {0}", $fileName)

$query = New-Object System.Management.ObjectQuery "SELECT * FROM SqlErrorLogEvent WHERE InstanceName=`'$instanceName`' AND Message Like `'%Error:%`' AND FileName = `'$fileName`'"

$scope = New-Object System.Management.ManagementScope "root\Microsoft\SqlServer\ComputerManagement11"

$searcher = New-Object System.Management.ManagementObjectSearcher $scope,$query

$resultCollection = $searcher.Get()

DisplaySqlErrorLogEventsContent($resultCollection)

[System.Console]::WriteLine(" ")

[System.Console]::WriteLine("Number of error record count: {0}", $resultCollection.Count)

[System.Console]::WriteLine(" ")

}

#Display each row

Function DisplaySqlErrorLogEventsContent($objCollection)

{

foreach ($sqlErrorLogEvent in $objCollection)

{

[System.Console]::WriteLine("Log Date :" + [System.Management.ManagementDateTimeConverter]::ToDateTime($sqlErrorLogEvent["LogDate"]))

[System.Console]::WriteLine(" ProcessInfo :" + $sqlErrorLogEvent["ProcessInfo"])

[System.Console]::WriteLine(" Message :" + $sqlErrorLogEvent["Message"])

[System.Console]::WriteLine(" FileName :" + $sqlErrorLogEvent["FileName"])

[System.Console]::WriteLine(" ")

}

}

$name = "MSSQLServer"

$fname = "ErrorLog"

if($first -ne $null)

{

$name = $first

}

if($second -ne $null)

{

$fname = $second

}

SqlErrorLogEvents $name $fname

#-------------------------end of Get-SqlErrorLogEvents.ps1------------------------

Support in SQL Server Management Studio (SSMS) LogViewer

These WMI classes are now leveraged inside of SSMS to launch Log viewer when SQL Server is offline or is not responding. If the Log Viewer is able to perform reading data through SQL Server connection, stored procedures like xp_enumerrorlog and xp_readerrorlog are used. If the Log Viewer determines that the connection to SQL Server could not be established, it falls back to using WMI route to read enumerate and read error logs.

Given that the WMI classes are used for log viewer, it requires that the WMI Service is started and only privileged Windows users who can access SQL Server logs will be able to view log data. Due to the sensitive nature of the data that is present in the logs, by default these privileges are given only to machine Administrators group by the SQL Server setup.

As a result SSMS should be launched using the “Run as administrator” option.

You can view the logs for a SQL Server instance in of SSMS from Registered Servers. To Register a SQL Server instance use the steps as described in the following link https://msdn.microsoft.com/en-us/library/ms188231.aspx

Once the SQL Server instance is registered, right-clicking on a registered a SQL Server instance displays the following context menu:

Choose “View SQL Server Log” to view the default current error log file.

The following error dialog is shown if the user does not have the required permissions to view SQL Server logs using Windows privileges, the following dialog will appear.

If the registered instance is a remote instance, there is an additional step that is required; the user is presented with a “Connect As” dialog as shown below:

Since SQL Server is offline, SQL Server credentials cannot be validated. After user enters valid Windows login credentials, a regular logviewer window with log events from current error log file as shown below:

The overall experience for viewing logs for an offline SQL Server instance is the same as that of an online / connected instance. We’ve also enabled operations like Export, Filter and Search operations side by side with Windows logs.

John Emmanuel Mandam (johnema@microsoft.com)