SQL Server 2012: Offline Logs viewing April 23rd, 2012

Vinod Kumar

As part of WMI events in SQL Server 2012, I saw these two new additions for offline error log files:

SqlErrorLogFile: http://msdn.microsoft.com/en-us/library/ff963576.aspx

SqlErrorLogEvent: http://msdn.microsoft.com/en-us/library/ff963582.aspx

Suddenly this got my attention and this blog post gets inspired because of the above events. These WMI classes are used inside SSMS’s Log Viewer when SQL Server is offline or is not responding. If the Log Viewer determines that the connection to a SQL Server instance could not be established, it will fall back to using WMI to enumerate and read the SQL error logs.

Since WMI classes are used for log viewing, it requires the WMI Service be started and only privileged Windows users with access to SQL Server logs can view the log data. By default the privileges are given only to the Administrators group of the machine by the SQL Server setup. As a result, SSMS must be launched using the Run as administrator option.

Offline Log Viewer can read error logs from SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Server 2012 versions.

How to use SSMS

With Microsoft SQL Server 2012, you can view SQL Server log files from a local or remote instance of SQL Server when the target instance is offline or cannot start. Steps to get this is:

  1. Start by opening SQL Server Management Studio (SSMS) as an Administrator.
  2. Register a SQL Server 2012 instance from Registered Servers (more about Registered Server on MSDN)
  3. Make sure the instance is stopped or offline.
  4. Once the SQL Server instance is registered, right-clicking on a registered SQL Server instance displays the following context menu to “View SQL Server Log
  5. The SQL Server ErrorLog is displayed like it normally would with an online instance.

Some of the things to consider before using the offline viewing capability are:

  1. The instance of SQL Server that you want to connect to should be registered in Registered Servers.
  2. The account that you use to connect must have the required permissions.

Final words

I found this feature quite helpful and for DBA’s this will be a great addition. You can access the offline log files from Registered Servers in SSMS (as in example above) or programmatically through WMI with WQL (WMI Query Language) queries. PowerShell can also leverage these WMI components to programmatically read the error log files. The other methods for access will be for a different blog post.

Feel free to drop your comments.

Tags: , , , , , , , , , ,

This entry was posted on Monday, April 23rd, 2012 at 22:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

3 Responses to “SQL Server 2012: Offline Logs viewing”

  1. Shashikant Shakya says:

    Thanks Vinod…for sharing this.

    nice feature… :)

  2. Anup Warrier says:

    This is option is really handy! I just tried, and found it really useful to admins during a crisis.

    Thanks for sharing this.

Leave a Reply