SQL Server Log Files

When you think about SQL Server log files, you might think first of the transaction log, which records recent database transactions and is used to ensure database integrity in the event of a system restore. However, many other log files also help to diagnose and troubleshoot problems. Here are five log files that play important roles in SQL Server 2005.

Update: SQL Server Log Files (2014)

5. SQL Server Setup Log

You might already be familiar with the SQL Server 2005 Setup log, which is located at %ProgramFiles%Microsoft SQL Server90Setup BootstrapLOGSummary.txt. If the summary.txt log file shows a component failure, you can investigate the root cause by looking at the component’s log, which you’ll find in the %Program-Files%Microsoft SQL Server90Setup BootstrapLOGFiles directory.

4. SQL Server Profiler Log

SQL Server Profiler, the primary application-tracing tool in SQL Server 2005, captures the system’s current database activity and writes it to a file for later analysis. You can find the Profiler logs in the log .trc file in the %ProgramFiles%Microsoft SQL ServerMSSQL.1MSSQLLOG directory.

Related: DBAs and SQL Server Logs

3. SQL Server Agent Log

SQL Server 2005’s job scheduling subsystem, SQL Server Agent, maintains a set of log files with warning and error messages about the jobs it has run, written to the %ProgramFiles%Microsoft SQL ServerMSSQL.1MSSQLLOG directory. SQL Server will maintain up to nine SQL Server Agent error log files. The current log file is named SQLAGENT .OUT, whereas archived files are numbered sequentially. You can view SQL Server Agent logs by using SQL Server Management Studio (SSMS). Expand a server node, expand Management, click SQL Server Logs, and select the check box for SQL Server Agent.

2. Windows Event Log

An important source of information for troubleshooting SQL Server errors, the Windows Event log contains three useful logs. The application log records events in SQL Server and SQL Server Agent and can be used by SQL Server IntegrationServices (SSIS) packages. The security log records authentication information, and the system log records service startup and shutdown information. To view the Windows Event log, go to Administrative Tools, Event Viewer.

1. SQL Server Error Log

The Error Log, the most important log file, is used to troubleshoot system problems. SQL Server retains backups of the previous six logs, naming each archived log file sequentially. The current error log file is named ERRORLOG. To view the error log, which is located in the %Program-Files%Microsoft SQL ServerMSSQL.1MSSQLLOGERRORLOG directory, open SSMS, expand a server node, expand Management, and click SQL Server Logs.

Related: 5 Important SQL Server Log Files