by Bill Wunder

Recently I covered some of what I have found to be the more useful and interesting system tables for the DBA. In the article Working with System Tables- Metadata Hierarchies there was a fairly detailed exploration of the login-user-object permission system data model and the Working with System Tables- Beyond the Basics article that followed considered some of the run time data that SQL Server makes available through views or virtual tables as well as the risks around and protocols for changing data in a system table.

There is another body of useful and often interesting even if static system generated data produced by SQL Server 2000 and stored in flat files at various places in the Server's local file system. Hopefully all readers are familiar with the SQL Server ErrorLog. This is without a doubt the most important SQL Server 2000 generated file. 

By default the ErrorLog file is found in the ..MSSQLLog folder created at the data path specified at installation. Unless you changed it during install it will be at the path C:\Program Files\Microsoft SQL Server\Mssql\Log\Errorlog (Note: When I say ..MSSQLLog in this section I am also implicitly referring to the similarly named relative paths for named instances that will include the instance name in the MSSQL folder such as ..MSSQL$INSTANCE2LOG.) It is possible to specify an alternate location and name for the ErrorLog file using the -e startup parameter for SQL Server, however, for the sake of consistency I would recommend against this unless you have a compelling reason. This log file not only provides details of errors as the file name name implies, but can also reveal such useful information as the current operating system and SQL Server version and Service pack installed, the number and affinity of CPUs for the instance of SQL Server, the status and time of recent backups, and even the port numbers that named instances might be using. I consider it an essential activity to review all new entries in the ErrorLog on a daily basis - at least for production servers - to assure the health of all SQL Servers. It's important to consider all entries with some care as it is quite easy to mistake a message that needs immediate attention for just another tersely innocent entry in the ErrorLog.

Also by default, a new ErrorLog is created each time SQL Server is started and the previous file is archived to a .1 extension. If a .1 archive already exists that existing .1 is renamed to a .2 extension. And still by default, there are 6 archive files kept plus the active ErrorLog. I have heard of more than one incident where a SQL Server is experiencing a problem and the DBA or other user will stop and start the database executable more than 6 times before anyone thinks to look in the ErrorLog for an indication of what the problem may be. Of course, by this time the initial error has been deleted from the system as after an archived ErrorLog is renamed to ErrorLog.6 it is deleted at the next startup of SQL Server. The first thing a wise DBA will do is look in the ErrorLog when troubleshooting a problem. Short of that best practice, it is also possible to increase the number of error logs saved as described in the KB article HOW TO: Archive More Than Six SQL Server Error Logs.

You can view the ErrorLog using Enterprise Manager, or you can dump it to the result pane in Query Analyzer using the extended stored procedure xp_readerrorlog, or you can use your favorite text editor such as notepad to open the file. Personally I favor the latter two methods because I find that Enterprise Manager doesn't always show you everything. In the past Enterprise Manager also added confusion by changing the order of display in some cases, but I haven't seen that problem for a the last service pack or two of SQL Server 2000.

One last useful detail concerning the ErrorLog is that in the hopefully rare event that your ErrorLog grows large and unwieldy yet you do not want to restart SQL Server to create a new smaller ErrorLog, you can use the system stored procedure sp_cycle_errorlog to create a new active log file that does not include the detail rows normally included at the beginning of each log file as the SQL Server instance starts. The most common and legitimate example here could be when using the 1204 trace flag to monitor deadlocks. The most common and illegitimate example might be a database logging 1105 errors (out of space) all night long.

The ErrorLog is not the only important file located in the ..MSSQLLog folder. The next most important log file in this directory is the SQLAGENT.out file. Like the ErrorLog, SQLAGENT.out uses an archive strategy to keep the previous 6 log files. You can view this file from the properties tab of the SQL Agent or you can go to the ..MSSQLLog folder and open it with a text editor. You can also specify a different path for this file from the SQL Agent properties dialog on the "General" tab.

There are two other log type files that may or may not show up in your ..MSSQLLog folder. One is named exception.log. The other is VDI.log.

When a SQL Server memory stack dump or mini dump is generated - usually when a memory access violation occurs indicating a misbehavior in the SQL Server executable's instructions - an entry should also be logged to the exception.log file indicating the error that caused the dump. Both the memory dump and the exception.log file are located in the ..MSSQLLog folder. If the exception.log does not exist it will be created. I do not find it useful to save .dmp files for an extended period of time and may remove them from the file system once I move to the next service pack level or in rare cases once there are simply too many 5MB memory dump files hanging around after a bout with a particularly insidious bug or kernel problem that may have presented me with a half a dozen or more trace dumps before the problem could be resolved. (The .dmp file itself is not terribly useful in the field. You may be able to glean a little from it, but mostly this is information that you can pass along to Microsoft's PSS engineer in the event you need to open a case with them to resolve the problem causing the memory failures.)

 VDI.log is a log file that is written with the other SQL Server error log files. The purpose of this file is to help diagnose backup and restore problems. The default path is C\Program Files\Microsoft SQL Server\MSSQL\Log. From personal experience, I know that Veritas and SQLLiteSpeed backup software can produce a VDI.log file. If you see this file repeatedly you may want to have it available when calling the backup software vendor to resolve the recurring problem,  problem.  While only unhandled application error will usually find their way to the Application Event Log, applications that use the SQL Server Virtual Device Interface API should always produce a VDI.log file when an API error is encountered. Usually this file is not all that useful to identify or resolve specific errors, but it is a good signal that the vendor software is where the problem is occurring and can be helpful to the vendor in determining where in their code the application is failing - and hopefully why. It's probably a long shot in most cases, but you may be able to download the SQL Server 2000 Virtual Backup Device Interface Specification and examine the documentation or look in the C:\Program Files\Microsoft SQL Server\80\Tools\DevTools\Include\vdierror.h file to get a better idea about a particular error you see in a VDI.log file.

There are SQL Server generated files at other locations worth looking at at the appropriate time. During the installation of each instance SQL Server creates two files - and overwrites any existing file by the same names - in the %SystemRoot% folder (usually C:\WinNT) that really should be reviewed immediately after the installation of each instance to make sure an error didn't slip by during install. One is unfortunately named setup.log - unfortunate because the name gives you no indication that SQL Server created the file and unfortunate because it is such a generic file name that another application could easily use the same name causing even more confusion than does the fact that each instance overwrites the file for a previous instance installation and unfortunate because SQL Server and Analysis Services both will create and overwrite this file. Fortunately this file is unlikely to contain lots of data so errors are pretty easy to spot in the unlikely event that one might occur. And fortunately, it is possible to specify an alternate location for this file using the -f2 switch for an Analysis Services install. The other is named sqlstp.log and contains a fairly verbose blow by blow listing of the file copies and registry activity that occur during an install. It's really not uncommon to see a suspicious looking entry or two in this file even when the installation goes as hoped. The key is to check the last line in this file and make sure it says "Installation Succeeded" or possibly something indicating a harmless error occurred such as "Installation Completed with Errors (number of nonfatal errors: 1) - Reboot Required" - meaning that the installers advice to stop a particular service such as SNMP or NetIQ was ignored and reboot is necessary to get the correct version of some .dll loaded into memory -  before you get too worried about any of the other entries in this log file. What you don't want to see on that last line is anything that begins, "INSTALL FAILURE:". 

Similarly, when you apply a Service Pack a file is created in the %SystemRoot% recording the actions of the Service Pack named sqlsp.log. As with the installation generated file sqlstp.log, look at the last line and verify that a fatal error was not recorded.

SQL Server version and Service Pack installations will also generate output (.out) and possibly error (.err) files in the SQL Server Program files folder you specified at installation time. Unless you changed it the path to such files will be the C:\Program Files\Microsoft SQL Server\MSSQL\Install folder. These output and error files will correspond to a sql script located in this folder and will containing the output and/or errors encountered when the script of the same name but with a .sql extension located in this same folder was executed.

Of particular interest for each Service pack are the .out files named 80spn-tools.out and spn_serv_uni.out - where n is the Service Pack number. I suggest always checking at least the files with these two names for any error messages and even better would be to check all .out files in the Install folder just to be safe after each installation and after the application of every Service Pack. I have seen more than one person discover that something went wrong with a service pack installation and they didn't notice the problem until after they had spent a significant amount of time troubleshooting a functionality issues perhaps months after the Service pack was applied. Note that Service Packs are cumulative so, for instance, when you run SP3 the scripts for SP1 and SP2 will be executed again and the original .out files from earlier Service Packs will be overwritten.

Similarly, a SQL Server version upgrade will generate output (.out) and possibly error (.err) files in the SQL Server Program files folder you specified at installation time. Unless you changed it, the path to such files will be C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade folder. Uniquely for upgrade processing, a new subfolder will be created each time the upgrade wizard is executed that will be include a date stamp component in the subfolder name. In this subfolder is where the upgrade output information files will be placed for that particular upgrade execution. There really are quite a number of files produced. I suggest you refer to the Books Online topic "Upgrade Log Files" for a complete listing.

Hot fixes - patches that you may need to apply between Service Packs - will usually generate a log file or two or even more in a subfolder of %SystemRoot% appropriately named SQLHotFix. So you would typically find hot fix output files at the path C:\WinNT\SQLHotFix\SQLHotFixn.Log where the n in the file name is a number ascending from 0 for each execution of a hot fix.

That's right! The hot fix log does not overwrite itself like the setup.log file does. And sequence numbers for output files for subsequent executions are in the form of a monotonically increasing postfix. Seems a little inconsistency between the ErrorLog, installations, Service Packs and hot fixes to me, but if you are on your toes and take steps to manually archive or at least review all log files before they get unexpectedly whacked, at the least you'll buy yourself some piece of mind, and you could save yourself a lot of trouble down the road. It's always easier to fix a problem when it occurs then it is to correct it after the system changes. 

One final thought here is that I suspect that the push by Microsoft to issue security patches in a timely manner will give rise to a patch process different than the current hot fix utility. Probably a good idea to keep you eyes peeled for yet another log file tucked away somewhere in the future.

Bill