By default SQL maintains 7 ErrorLogfiles and rotates them, trowing away the oldest log and creates a new one. If logfiles grow big in size and ‘eat up’ all your HDD space you can force the logfile rotation.
The default logfile names are:
ErrorLog
ErrorLog.1
ErrorLog.2
ErrorLog.3
ErrorLog.4
ErrorLog.5
ErrorLog.6
The normal logfile cycle goes like this:
All data in ErrorLog.6 is removed and a new ErrorLog file is created.
All data in ErrorLog goes to file ErrorLog.1
All data in ErrorLog1. goes to file ErrorLog.2
All data in ErrorLog.2 goes to file ErrorLog.3
All data in ErrorLog.3 goes to file ErrorLog.4
All data in ErrorLog.4 goes to file ErrorLog.5
All data in ErrorLog.5 goes to file ErrorLog.6
To force the logfiles to rotate you have to do the following:
Open SQL Server Management Studio and log in.
Execute this query:
sp_cycle_errorlog
If you have a particularly big logfile for instance ErrorLog.3 you have to execute the query multiple times to get rid of the ErrorLog.3!