If you come across a SQL Server that is not running, it can be troublesome to determine what happened and when. You can run through the Windows event viewer, but this can be a real needle in a haystack problem on busy servers, especially if there are a lot of instances. Wouldn't it be great to be able to easily read the SQL Server error log, even though the server is down? Currently you would typically do this through xp_read_errorlog – which requires the server to be up. So we've already talked about a needle in a haystack problem, now we also have a chicken and egg problem.
In Management Studio, starting with SQL Server 2012, you may notice a slightly different context menu when you right-click an instance in Registered Servers (granted, I think it needs to already be registered before you try to troubleshoot it being offline):
Notice the highlighted menu option "View SQL Server Log" – enabled even for a server that Management Studio is not online. But don't press it yet, unless you have started SSMS as administrator, by selecting the following option from the right-click context menu:
If you haven't launched SSMS as administrator, you will get the following error message:
This is because the error log is read via WMI. One limitation of this minor implementation detail is that you won't be able to use this feature if you launch SSMS from a regular shortcut that has been pinned to the taskbar in Windows 7 – right-clicking does not provide an option to Run as administrator. I hope the manageability team makes it more obvious that run as administrator is required – I guess a better error message will do.
When the stars align, you will get the following:
This means you can review the last instance of the error log even though the service is currently offline; you can also go back and review any log file that hasn't been cycled. Cool, right? Of course, it requires that the Windows server hosting the instance of SQL Server is running.