Troubleshooting database problems is never an easy task. When you’re the DBA or the SQL Server pro who’s fixing problems, you need all the information you can find. Fortunately, SQL Server generates several different log files that can help you solve a variety of problems ranging from setup issues to server and application errors. These logs exist in all the recent releases of SQL Server; with SQL Server 2012 and SQL Server 2008 R2, you can use registered servers to view SQL Server log files.
Related: SQL Server Log Files
To view the operational logs in SQL Server 2012, open SQL Server Management Studio (SSMS) and expand the Management node. Right-click the SQL Server Logs node and select View, then select SQL Server and Windows Log from the context menu. If you’re using an earlier version of SQL Server, you can navigate directly to the directory containing the log file. Most log files can be opened using Notepad. Let’s dive into some of the most important log files for SQL Server troubleshooting.
Windows Event Log
Although it’s not used exclusively by SQL Server, the Windows Event log is an important source of information for troubleshooting SQL Server errors. The Windows Application log records events for SQL Server and SQL Server Agent, and it can also be used by SQL Server Integration Services (SSIS) packages. SQL Server events are identified by the entry MSSQLServer or MSSQL$<instance name>. SQL Server Agent events are identified by the entry SQLServerAgent or SQLAgent$<instance name>. By default, the Windows Event logs are located in the \%SystemRoot%\System32\Config directory. You can use the Windows Event Viewer to view the Windows Event log from the Control Panel Administrative Tools applet’s Event Viewer option or from the SQL Server Log Viewer on SQL Server 2008 R2 and later.
SQL Server Error Log
The most important log file used by SQL Server is the Error log. For SQL Server 2012, the Error log is found in the \%ProgramFiles%\Microsoft SQL Server\MSSQL11\MSSQL\LOG\ERRORLOG directory. Different versions of SQL Server use different directory numbers: SQL Server 2014 uses directory number MSSQL12. SQL Server 2012 uses MSSQL11, SQL Server 2008 R2 uses MSSQL10_50, SQL Server 2008 uses MSSQL10, and SQL Server 2005 uses directory number MSSQL1.
The current Error log file is named ERRORLOG. SQL Server retains backups of the previous six logs, naming each archived log file with a sequentially numbered extension. You can view the Error log by opening SSMS, expanding a server node, then expanding the Management node and clicking SQL Server Logs. Or you can just open the ERRORLOG file using Notepad.
Error Logs and select the log. You can also open the SQLAGENT log files using Notepad.
Related: How to prevent enormous SQL Server error log files
SQL Server Agent Error Log
SQL Server Agent is a job scheduling subsystem. It writes in its log files any warning and error messages pertaining to the jobs it runs. As with the SQL Server Error log, the SQL Server Agent Error log files for SQL Server 2012 are, by default, written to the \%ProgramFiles%\Microsoft SQL Server\MSSQL11\MSSQL\LOG directory. The different versions of SQL Server use the same directories as the SQL Server Error log. SQL Server maintains up to nine SQL Server Agent Error log files. The current log file is named SQLAGENT.OUT, whereas archived files are sequentially numbered. You can view SQL Server Agent Error logs on SQL Server 2012 by using SSMS: Expand a server node, expand SQL Server Agent, then expand
SQL Server Profiler Logs
SQL Server Profiler is the primary application tracing tool, and you can use it to troubleshoot your database applications. SQL Server Profiler captures the server’s current database activity and writes it in a file for later analysis. As with the SQL Server Error log and the SQL Server Agent Error log, the SQL Server Profiler logs for SQL Server 2012 are found in the \%ProgramFiles%\Microsoft SQL Server\MSSQL11\MSSQL\LOG\ directory.
SQL Server Profiler logs for SQL Server 2012 are named using the convention log_<log number> with the .trc extension. Double-clicking a log opens the SQL Server Profiler interface that helps you analyze the log file.
SQL Server Setup Log
If you’ve ever had trouble completing the SQL Server or SQL Server Express setup, you can determine the problem by examining the SQL Server Setup log. The SQL Server 2012 Setup log can be found at \%ProgramFiles%\Microsoft SQL Server\110\SetupBootstrap\LOG\Summary.txt.
Different SQL Server releases use different version numbers in the directory. For instance, SQL Server 2014 is directory number 120, SQL Server 2012 is directory number 110, SQL Server 2008 is directory number 100, and SQL Server 2005 is directory number 90. If the summary.txt log file shows a failure for a component, you can investigate the root cause of the failure by looking at the log for that component. Each of the separate component logs can be found in the \%ProgramFiles%\Microsoft SQL Server\120\Setup Bootstrap\LOG\Files directory.
