SQL Server Error Logs

SQL Server Error Logs

* Error Logs maintains events raised by SQL Server database engine or Agent.

* Error Logs are main source for troubleshooting SQL Server problems.

* SQL Server supports 2 types of error logs

                * SQL Server Logs

                * SQL Agent Logs

What is recorded in error logs?

1.       SQL Server start up events including database recovery.

2.       Backup and restore details.

3.       Any failed SQL Server jobs

4.       User defined error message which has WITH LOG clause.

5.       Maintenance related DBCC statements, such as DBCC CHECKDB and DBCC CHECKALLOC.

6.       Turning trace flags on or off.

7.       SQL Servers usage of a particular session for a long period of time.

8.       Starting and stopping Profiler traces

* By default SQL Server supports

                1 - Current Log

                6 - Archieve Logs

* Error logs are present in LOG folder of respective instance.

* We can read error logs using

                sp_readerrorlog

                xp_readerrorlog

* By default when the server was restarted the error logs are recycled automatically. We can recycle error logs using

                sp_cycle_errorlog

* We can configure up to 99 error logs