Saturday, 10 October 2015

Cycling SQL Server error log







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








  


1 comment: