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.
Hi Aaron, have you seen if Microsoft Codename 'Atlanta' project beta.microsoftatlanta.com already has the feature of viewing SQL Server error log or providing error details or even alerting you by email? If not probably some corrections in SCOM management pack could do this.
Eric, when I shift + right-click, this is what I get:
http://twitpic.com/369fctIt seems to only work if you don't already have a copy of SSMS open (I usually have at least one or two open at any time).
If you shift-right-click the shortcut, run as Administrator is available from a pinned task bar shortcut.
You're right Chris, I was mixing up my log file metaphors. One barrier to opening up the errorlogx files, though, is locating them – with the instance down, you'll need to go to the registry to figure out where they are. Reading the files in notepad works, I guess, but not a rich or familiar experience – you can't search, filter, export, sort, etc.
Hey Aaron – you say that the log is in a proprietary format – so is this different than just looking at the ERRORLOGx text files?