SQL SERVER – Working with ERRORLOG.

In this article we are going to see how we can work with ERRORLOG. What is the ERRORLOG? The ERRORLOG, is the SQL Server Log, it means, the place where we can check the errors for SQL Server.

SQL Server has several ERRORLOG files, by default each instance contains 6 ERROLOG files, it value can be changed and we are going to see how to do it, later. Every time that SQL Server starts a new ERRORLOG file is generated and the current ERROLOG is renamed to ERROLOG.1; and the ERRORLOG.1 is renamed to ERRORLOG.2 and so on.

How to see ERRORLOG content?

To see the ERRORLOG we can use the Microsoft SQL Server Management Studio (SSMS) or using TSQL.

Using TSQL:

We can use a extended stored procedure called xp_readerrorlog, it display the errors existing on the last ERRORLOG file.

xp_readerrorlog

Results:

LogDate ProcessInfo Text ----------------------- ------------ ----------------------------------------------------- 2009-07-01 18:35:02.150 spid53 Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) 2009-07-01 18:35:02.150 spid53 (c) 2005 Microsoft Corporation. 2009-07-01 18:35:02.150 spid53 All rights reserved. 2009-07-01 18:35:02.150 spid53 Server process ID is 1208. 2009-07-01 18:35:02.150 spid53 Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.

Using SSMS:

We can check one file or all the files mixed in the following path: SSMS->Management->SQL Server Logs.

How to see ERRORLOG files list?

To know the file name, creation date and file size for the errorlog files in my SQL Server instance, we can use the stored procedure called xp_enumrrorlogs, it does not need any input parameter.

exec xp_enumerrorlogs

Results:

Archive # Date Log File Size (Byte) ----------- --------------------- -------------------- 0 07/01/2009 18:35 1348 1 07/01/2009 18:35 1616 2 07/01/2009 17:42 1616 3 07/01/2009 17:40 1616 4 07/01/2009 17:27 14222 5 06/29/2009 07:19 13004 6 06/28/2009 00:06 2588 (7 row(s) affected)

Obviously, we can use Windows Explorer J as well. In the results of xp_readerrorlog we can see where the errolog files are located.

How ERRORLOG can be Cycling/Purge/Reset?

As is mentioned on the beginning of this article, every time that SQL Server is started the ERRORLOG is purge and a new ERRORLOG file is generated.

When a SQL Server instance is up and running for long periods of time, the errorlog file grow up considerably and it cause hard to read the entire file. SQL Server offers the capability to cycle the ERRORLOG in a manual way, to perform this task we can use the system stored procedure called sp_cycle_errorlog.

exec sp_cycle_errorlog

Result.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

When sp_cycle_errorlog is executed successfully, the current ERRORLOG file is renamed to ERRORLOG.1 and the same behavior happens with the rest of ERRORLOG files. The last ERRORLOG file is deleted.

Another way to purge the SQL Server log is using the DBCC ERROLOG. This DBCC is used when the stored procedure sp_cycle_errorlog is executed.

DBCC ERRORLOG

How many old ERRORLOG are needed?

If the plan is to cycle/purge/reset the errorlog file in a manual way, is necessary keep in mind the timeframe to generate a new log file. Also is necessary to know the frequency to reset the log and the amount of information necessary to hold.

If the plan is to hold more files than the default. We can change using the SSMS->Management -> SQL Server Logs, then right click and choose the Configure option. The values allowed are from the default (6) until 99, it value is the maximum value allowed by SQL Server.

Conclusion

Having an ERRORLOG file in a reasonable size, it makes easier to browse and analyze the log error. This article show us how we can work with the errolog file: cycling the file with sp_cycle_errorlog, reading the file with xp_readerrorlog, listing the files with xp_enumerrologs and changing the total of errorlog files.