During my SQL Server career I’ve seen many sites where recycling the error-log(s) was not in place, making browsing of the log from very cumbersome to almost impossible due the large amount of history data that has become meaningless over time.
Recycling the logs can be done in two steps:
EXEC dbo.sp_cycle_agent_errorlog ;
EXEC dbo.sp_cycle_errorlog ;
I normally schedule these two stored-procs as a SQL Server Agent job once a week on a Sunday night, so the log is fresh at the beginning of the week.
According to books on-line:
SQL Server retains backups of the previous six logs and gives the most recent log backup the extension .1, the second most recent the extension .2, and so on. The current error log has no extension
This approach leaves me with of log history of seven weeks (instance restarts not counted of course as this also recycles the log), leaving me with enough info to browse at high speed.
