* 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
