Making the SQL Server error log easier to view

The SQL Server Error log

At some point every DBA will have had to look at the current SQL Server error log using their favourite text editor. However, as the default error log file is literally just called errorlog (with no file extension), there’s the awkward process of selecting the application with which to open the file every time you need to view it, with no option to associate errorlog with your favourite text editor thanks to the lack of file extension. If it’s a 3am callout and the server is down, you can’t use Management Studio to view and filter the log so your only resort is to manually load the file and anything that makes this easier is always helpful.

Like all good solutions, the answer is deceptively simple and I use it whenever I build a SQL Server instance (and it also has an added benefit I’ll describe later); change the default error log filename to have an .txt or .log extension. It’s an old trick I learnt from my Sybase days and is still very much relevant to the latest and greatest versions of SQL Server (2008 R2, as of the time of writing).

Tip: it’s also a good way of modifying the default error log folder location.

It’s achieved by editing the command line in SQL Server Configuration Manager (SSCM) and editing the default error log location hard-coded in that command line to something more user-friendly. Double-click the SQL Server instance you want to edit in SSCM, under the SQL Server Services folder to bring up the properties, click on the Advanced tab and the command line can be edited under Startup Parameters. Before you do anything, save the current parameters to a text file.

The default SQL Server command line has 3 basic components: the name and location of the master database primary data file, the name and location of the master database transaction log file, and the name and location of the error log. These are represented by the -d (data file) -l (log file) and -e (error file) parameters in the command line. There are other command line options like -E for large page extents and -T for trace flags, as well as options for starting in single user mode and specifying named instances etc etc but they’re all well outside the scope of this post (I’m not mentioning them to show off what I know about the command line, but just to highlight the most likely additional parameters you may come across on any of your servers that you want to make this modification on).

Some DBAs are terrified of playing around with the SQL Server command line, but all we’re interested in is modifying the -e parameter, which by default will be something like

-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG;

All we’re interested in is modifying the bit between -e and the semi-colon. Simply adding .LOG or .TXT (see Fig. 1) will make your life so much easier when you have to peruse the log file contents of either the current log or rolled-over logs as you can bypass SSMS and just use your preferred text editor.

Figure 1: Modifying the startup properties (SQL Server 2008R2)

After changing the error log name represented by the -e parameter a restart will be required for the change to take effect, so don’t do this on production systems until you’ve tested on development and staging systems first.

Tip: prefixing the name of the host/instance to the error log name makes it much easier to identify the server concerned if you copy log files down to your desktop machine or a central server.

That added benefit? Not only can you alter the filename of the error log, but you can also alter the path, which means you can keep the SQL Server error logs, SQL Server Agent error logs and default trace files in a separate location, as it is this folder that you specify for the error log that dictates where these other files get written to.

Disclaimer: do this entirely at your own risk. I’ve carried these instructions out on production systems on places I have worked out and am comfortable with making these changes. Test them on development/staging servers first if you’re doing this for the first time or are unsure about making changes.

There’s also a Connect enhancement request to give the error log an extension by default as well as to add the instance name to the name of the log, so feel free to vote for it on the Microsoft Connect site.

Related articles

Finding the sql error log folder
A simple way to get the default SQL Server data and log path