Cycling The Error Logs

Error Logs? Surely you don't have errors? :)

Well, SQL Server records pretty much everything, so it can get a bit unmanageable. If there are a million errors in your log file, it takes a long time to open that file and tell you what is happening. So I have a two step agent job that runs each week - DBAdmin Cycle Error Logs

Step 1 : execute dbo.sp_cycle_errorlog
Step 2: execute dbo.sp_cycle_agent_errorlog

SQL Server by default retains 6 logs, and although you can configure it to retain more, I can't imagine a reason for wanting to look at an error from more than six weeks ago.  

However, on some of my servers, my job fails - Step 1 works, Step 2 fails with an access denied message:

Executed as user: Domain\Server_Agent. SQLServerAgent Error: Access is denied. [SQLSTATE 42000] (Error 22022). The step failed.

I noticed that the error log is actually being cycled. 

The error message relates to the system attempting to write to the log to say what it has done

In my case, the system is logging SQL Server messages in file: 

'E:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG'.

So, as so often with weird stuff in SQL Server, it's a permissions issue

I set list permission for the SQL agent account all the way to:
E:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL
then set Modify on the Log folder

I ran the job again, it failed again, argh!

And here’s the crucial bit that I didn’t do at first:

Restart SQL Server Agent Service

I ran the job again, and it worked