How to Recycle SQL Server Error Log file without restarting SQL Server Service
Sept 15, 2014
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 everytime SQL Server Database Engine is restarted. 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.
Note:- 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.
Recycle SQL Server ErrorLog File using DBCC ERRORLOG Command
Execute the below TSQL code in SQL Server 2012 and later versions to set the maximum file size of individual error log files to 10 MB. SQL Server will create a new file once the size of the current log file reaches 10 MB. This helps in reducing the file from growing enormously large.
Recycle SQL Server Error Log File using SP_CYCLE_ERRORLOG System Stored Procedure
Best Practice: 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.
This article explains how to Recycle SQL Server Error Log file without restarting SQL Server Service.
MORE SQL SERVER PRODUCT REVIEWS & SQL SERVER NEWS
- Performance Dashboard Reports in SQL Server 2012
- Using WITH RESULT SETS Feature of SQL Server 2012
- Indirect Checkpoints in SQL Server 2012
- SQL Server Paging Using OFFSET and FETCH Feature in SQL Server 2012
- Download SQL Server 2012 System Views Map
- Contained Databases SQL Server 2012
- How to Quickly Capture Diagnostic Data and Health Information SQL Server 2012
- SQL Server 2012 Build List
- New Date and Time Functions in SQL Server 2012
- New Features in SQL Server 2012 Reporting Services for Developers
- New Features in SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- Read More SQL Server Articles…