How To Safely Delete SQL Server ErrorLog Files

Closes the current error log file and cycles the error log extension numbers just like a server restart. The new error log contains version and copyright information and a line indicating that the new log has been created.

One of my SQL Server ErrorLog files has grown to a very large size.  I'd like to delete it to free space on my hard drive.  Can the SQL Server ErrorLog file be safely deleted without harming SQL Server?

ANSWER:

By default SQL Server stores seven ErrorLog files named:

• ErrorLog
• ErrorLog.1
• ErrorLog.2
• ErrorLog.3
• ErrorLog.4
• ErrorLog.5
• ErrorLog.6

In SQL server 2008, the ErrorLog files are stored in the C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log folder.  The ErrorLog file contains the newest information; the ErrorLog.6 file contains the oldest information.

Every time SQL Server is restarted, the log files cycle as follows:

• All data in the ErrorLog.6 file is deleted and a new ErrorLog file is created. 
• All data in the previous ErrorLog file is written to the ErrorLog.1 file. 
• All data in the previous ErrorLog.1 file is written to the ErrorLog.2 file. 
• All data in the previous ErrorLog.2 file is written to the ErrorLog.3 file. 
• All data in the previous ErrorLog.3 file is written to the ErrorLog.4 file. 
• All data in the previous ErrorLog.4 file is written to the ErrorLog.5 file. 
• All data in the previous ErrorLog.5 file is written to the ErrorLog.6 file.

If one of the ErrorLog files has grown to a large size, the ErrorLog files can be manually cycled by running the sp_cycle_errorlog Stored Procedure.   

Note:  The data in the older ErrorLog files will be overwritten!  Copy the older ErrorLog files to some external media if they must be saved.

In SQL,the sp_cycle_errorlog Stored Procedure can be run from SQL Server Management Studio.

EXAMPLE :

EXEC sp_cycle_errorlog ;
GO