The error log is a central file(s) in which all the important actions and error messages concerning the database are recorded. The database administrator should examine the log every day.
All the available error logs are listed.
A new error log is created each time SQL Server starts. SQL Server retains backups of the previous six error logs. If the log file name is E:\ Program Files\Microsoft SQL Server\LOG\ERRORLOG, the last six log files are archived under the following names:
E:\ Program Files\Microsoft SQL Server\LOG\ERRORLOG.1
E:\ Program Files\Microsoft SQL Server\\LOG\ERRORLOG.2 …
E:\ Program Files\Microsoft SQL Server\\LOG\ERRORLOG.6
Each time the SQL Server starts, the archived oldest log files are overwritten and lost, unless you save them first. If you have a serious problem, you should immediately save these files otherwise they are lost.
Pay special attention to the columns Contains Severity 17 and Contains Severity 20. If a Y appears in these columns, it means a noteworthy severity level error message has occurred, and the cause should be investigated.
Newer versions of the application allow you to select checkboxes to include only severity 20 or 17 errors, or to exclude backup and logon messages.