SQL Server error logs

The SQL Server error log

As well as events, the SQL Server startup conditions, the results of backup and restore operations, automatic recovery messages, kernel messages, errors in user connections, server-level error messages, the results of batch commands, or other scripts and processes are written to the SQL Server Error Log.

The SQL Server Error log is written out as a plain ASCII file and is located at ...

....Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\ERRORLOG and ERRORLOG.n files.

A new error log is created each time an instance of SQL Server is started. Typically, SQL Server retains backups of the previous six logs and gives the most recent log backup the extension .1, the second most recent the extension .2, and so on. The current error log has no extension. The errors in the Error log duplicate those in the Windows Application event log.

SQL Server Agent Error Log

SQL Server Agent has its own error log (SQLAGENT), typically in the same location as the SQL Server error log. The current log file is SQLAGENT.OUT. SQL Server maintains up to nine SQL Server Agent error logs. Each archived log has an extension that indicates the relative age of the log. For example, an extension of .1 indicates the newest archived error log and an extension of .9 indicates the oldest archived error log.

SQL Server Setup Log

The SQL Server 2005 Setup log is usually located at %ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Summary.txt. This SQL Server Setup Log

Will show a component failure, the details of which will be found in that component's log, which is in the ...

...%Program-Files%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Files directory.

SQL Server Agent service history log

The progress and outcome of all jobs is stored in system tables in msdb. These also contain a description of any errors, so should be monitored.