The Microsoft® SQL Server™ error log can be viewed to ensure that processes have completed successfully (such as backup and restore operations, batch commands, or other scripts and processes) and to detect any current or potential problem areas, including automatic recovery messages (particularly if SQL Server has been stopped and restarted), kernel messages, and so on.
The SQL Server error log can be viewed using SQL Server Enterprise Manager or any text editor. By default, the error log is located at \Mssql7\Log\Errorlog.
A new error log is created each time the computer running SQL Server is started, although the sp_cycle_errorlog system stored procedure can be used to cycle the error log files without having to restart the computer running SQL Server. 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.
To view the SQL Server error log
To cycle the SQL Server error log