Understanding the SQL Server error Logs

Error Logs name itself defines as it will logs the error events raised by SQL Server database engine or SQL Server Agent.

 Error Logs are the main source to troubleshooting the SQL Server problems.

 SQL Server supports 2 types of error logs

                * SQL Server Logs

                * SQL Agent Logs

What is recorded exactly 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 per instance.

Things about Error Logs everyone should know

How to View SQL Server Error Log file location

EXEC xp_readerrorlog 0,1,"Logging SQL Server messages in file";

You can change the default location of SQL Server error logs by using Startup parameter -e
“-e C:\Logs\NewLogs”

How to see the error logs location without SSMS
Open SQL Server Configuration Manager
Go to Start > All Programs > Microsoft SQL Server 2005 (or 2008) (or 2008 R2) > Configuration Tools > SQL Server Configuration Manager

How to view the SQL Server error log using management studio –
Connect to SQL Server > In Object Explorer > Expand a server > Expand Management > and then Expand SQL Server Logs. Right-click a log and click View SQL Server Log.

By Default SQL Server maintains 6 Error log files only. Default settings can be changed to any number between 6-99.

To change the default settings
Connect to SQL Server > In Object Explorer > Expand a server > Expand Management > and Right-click > Configure > Check the Limit box and change.

There are problems with the size of error logs. One error log file can grow up to any limit if SQL server is not restarted since a long time. On the other hand, a file can be very small if SQL server is restarted frequently.

As a best practice – SQL Server error logs can be recycled by creating a SQL Job which runs at a regular interval. That’s how you can prevent the problems of loading or reading the error log.

SQL Server error logs can give you information about machine type. Use the script below.

 EXEC xp_readerrorlog 0 ,1 ,"Manufacturer";

 SQL Server error logs can give you the port number which is used by SQL Server

 EXEC xp_readerrorlog 0 ,1 ,"Server is listening on";

SQL Server error logs can give you information about Startup Parameter that are used by SQL Server

EXEC xp_readerrorlog 0,1 ,"Registry startup parameters";

SQL Server error logs can give you information about Dedicated admin connection is used by SQL Server

 EXEC xp_readerrorlog 0 ,1 ,"Dedicated admin connection support";

SQL Server error logs can give you information about OS Process ID used by SQL Server

EXEC xp_readerrorlog 0,1 ,"This instance of SQL Server last reported using a process ID";

SQL Server error logs can give you information about SQL Server authentication Mode

EXEC xp_readerrorlog 0,1 ,"Authentication mode";