Friday, June 7, 2013

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

FAQ : - 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

* How to Configure?
                * Go to Object Explorer
                * Management
                * R.C on SQL Server Logs
                * Configure
                * Select checkbox " Limit the no of error logs..................."
                                Max no of logs= 20
                * OK

* To filter the events from error log
                sp_readerrorlog 0,1,'Error',null

Value of error log file you want to read:
                                0 = current, 1 = Archive #1, 2 = Archive #2, etc...

Log file type: 1 or NULL = error log, 2 = SQL Agent log
Search string 1: String one you want to search for
Search string 2: String two you want to search for to  further refine the results
                5th         : Start Date
                     6th         : End Date

No comments:

Post a Comment

Note: Only a member of this blog may post a comment.

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...

SQL Server DBA Training

SQL Server DBA Training
SQL Server DBA