How To Recycle Error Log in SQL Server

SQL SERVER –How To Recycle Error Log

In this short post I am going to talk about Error logs which SQL Server maintains and how can we manage it.The SQL Server error log contains user-defined events and certain system events. We use this error log to troubleshoot problems related to SQL Server. The SQL Server Error Log is a great place to find information about what's happening on your database server.

To view the SQL Server error log:

1. In Object Explorer, expand a server, expand Management, and then expand SQL Server Logs.2. Right-click a log and click View SQL Server Log.

IMAGE: 1

MS SQL Server uses 7 files to store these messages. The first file serves as the current error log file, it records every new message to this. The other 6 files are archived files, and they contain previous messages.

Every time when SQL Server is restarted, it recycles the files. It means that after restarting the system the current log file is new log file which is created after restarting the system and the current log file which was there before restarting the system becomes Archive #1 and Archive #1 becomes Archive #2 and so on.. and the oldest log file ("Archive #6") is deleted.

If you want to increase the number of error log files you can change it to any number between 6 and 99. The way to do it is by right-clicking the "SQL Server Logs" folder in SSMS and choosing "Configure". A window will appear as follows:

IMAGE:2

Now check to the option and go ahead with your number.

The real problem comes when you are working with production databases where they do not restart the system everyday so the current log file size increases and It takes so much time when we try open that. The default location of the log file is : "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log" (for a SQL Server 2008 R2 instance).

To make the error log file shorter we have two other options either we can set up Sql server agent job which will do the recycling based on the scheduled time or we can manually do it via system store procedure i.e. : sys.sp_cycle_errorlog.

Now to do it manually we can run the system procedure sys.sp_cycle_errorlog as follows:

Open a new Query window and run :

EXEC sys.sp_cycle_errorlog

Now you can see the new current log file has a new time stamp in below screen shot. You can match it with first image. This is how we can recycle it without restarting the system.

Thanks for reading this article. Suggestion and most welcome!!!!