It is a good practice to cycle sql server error log as also it is easy to handle error logs of small size , By default sql server will only cycle error logs once its starts , so for easy managing of error logs its better to cycle error logs , in most of the productions system we create job to cycle error logs that runs daily /weekly/ monthly depends on the volume of contents that will be written to the error logs. Once the error log cycle the current error log is renamed as errorlog.1 ,
We can use stored procedure ' sp_cycle_errorlog' to cycle error log , members of sysadmin fixed roles can trigger this procedure , so by using the below T-sql we can cycle the error log
exec sp_cycle_errorlog
go
By creating a job with above query we can automate this task
By default sql server retains current and 5 recent error logs , this can be changed in SSMS by expanding Management\Sql Server log [right clich ] -> configure select Limit The Number Of Error Logs Before They Are Cycled, and select the maximum number of error logs, as shown in Figure , Minimum limit is 6 and maximum is 99
