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.
5. SQL Server Setup Log
You might already be familiar with the
SQL Server 2005 Setup log, which is
located at %ProgramFiles%\Microsoft SQL
Server\90\Setup Bootstrap\LOG\Summary.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 Server\90\Setup
Bootstrap\LOG\Files 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 Server\MSSQL.1\MSSQL\LOG
directory.
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
Server\MSSQL.1\MSSQL\LOG 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 Integration
Services (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 Server\MSSQL.1MSSQL\LOG\ERRORLOG directory,
open SSMS, expand a server node, expand
Management, and click SQL Server Logs
End of Article
