Wednesday 14 April 2010

Problems recycling SQL Server logs

Sometimes it is possible that SQL Server has problems cycling the error or SQL Agent logs. This can result in maintenance plans reporting failure.

These errors may manifest themselves on the server as missing files in the log sequence (e.g. there is an ERORLOG file, ERRORLOG.1 is missing but there is an ERRORLOG.2 etc.). Occasionally the problem can be fixed by renaming the files so the sequence is compete again.

To recycle error or SQL Agent log manually try running one of the following:

EXEC sp_cycle_agent_errorlog

EXEC sp_cycle_errorlog


You can also recycle the SQL Server Agent log via the Management Studio by right-clicking on SQL Server Agent > Error Logs, and choosing recycle from the popup menu. However, it is possible that the log simply won’t recycle:


In this case a safe bet is to restart the SQL Server Agent service.