SQL Server Error Logs
* Error Logs maintains events raised by SQL Server server or SQL Server Agent.
* Error Logs is main source for troubleshooting and identifing SQL Server problems.
* SQL Server supports 2 types of type error logs
* SQL Server Logs
* SQL Agent Logs
Questions in Interview : – 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.location to find
* We can read error logs using
* We can configure up to 99 error logs
* By default when the server was restarted the error logs are recycled automatically. We can recycle error logs using
* How to Configure?
* Go to Object Explorer
* R.C on SQL Server Logs
* Select checkbox ” Limit the no of error logs……………….”
Max no of logs= 20
* To filter the events from error log
Value of error log file you want to read:
0 = current, 1 = Archive #1, 2 = Archive #2, and so on…
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