SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

How to Recycle SQL Server Error Log file without restarting SQL Server Service

SQL Server Error Log is the best place for a Database Administrators to look for informational messages, warnings, critical events, database recover information, auditing information, user generated messages etc. SQL Server creates a new error log file every time SQL Server Database Engine is restarted.

Recycle SQL Server Error Log file

This article explains how to Recycle SQL Server Error Log file without restarting SQL Server Service.

Database administrator can recycle SQL Server Error Log file without restarting SQL Server Service by running DBCC ERRORLOG command or by running SP_CYCLE_ERRORLOG system stored procedure.

Starting SQL Server 2008 R2 you can also limit the size of SQL Server Error Log file. For more information see Limit SQL Server Error Log File Size in SQL Server. However, to increase the number of error log file see the following article for more information How to Increase Number of SQL Server Error Log Files.

Limiting SQL Server Error Log File Size

Execute the below TSQL code in SQL Server 2012 and later versions to set the maximum file size of individual error log files to 20 MB.

SQL Server will create a new file once the size of the current log file reaches 20 MB. This helps in reducing the file from growing enormously large.

We recommend you to read “Limit SQL Server Error Log File” to know how to identify the registry path for your instance of SQL Server and make changes to the below script to update the registry.

Before you make changes to Registry Settings it is recommended to Backup Registry. Learn How to back up and restore the registry in Windows

USE [master];
GO

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE'
    ,N'Software\Microsoft\MSSQLServer\MSSQLServer'
    ,N'ErrorLogSizeInKb'
    ,REG_DWORD
    ,20480;
GO

Recycle SQL Server ErrorLog File using DBCC ERRORLOG Command

USE [master];
GO

DBCC ERRORLOG
GO

Recycle SQL Server Error Log File using SP_CYCLE_ERRORLOG System Stored Procedure

Use [master];
GO

SP_CYCLE_ERRORLOG
GO

Best Practice for Recycle Error Log SQL Server

It is highly recommended to create an SQL Server Agent Job to recycle SQL Server Error Log once a day or at least once a week.

Conclusion

In this article explains how to Recycle SQL Server Error Log file without restarting SQL Server Service.

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement