Friday , October 20 2017
Home / MSSQL DBA / SQL Server Error Logs Detailed Information

SQL Server Error Logs Detailed Information

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 SQL Server Error Logs
* We can read error logs using
xp_readerrorlog
sp_readerrorlog

* 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
sp_cycle_errorlog

* 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, 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

Read More:
Undo Tablespace
Managing Security for Oracle Database Users
Oracle Tablespace Management

Comments

comments

Check Also

SQL Server 2012 Installation Step by Step with Screenshots

SQL Server 2012 Installation Step by Step with Screenshots

SQL Server 2012 Installation. Steps to install SQL Server 2012, with complete screenshots. Click on …

Leave a Reply

Your email address will not be published. Required fields are marked *