Cycle Agent ErrorLog Fails, any Long term solution found?

Question

Post reply

Cycle Agent ErrorLog Fails, any Long term solution found?

Staggerlee

SSCertifiable

Points: 5272
More actions
September 29, 2014 at 4:15 am

#290437

Hi all,
So we have set up nightly cycling of the SQL and Agent errorlogs, via an Agent job thatruns at 00:00 step 1 cycle the SQL error log, Step 2, cycle the Agent error log.
We are finding a lot of the boxes are failing on the Agent Error Log, the only solution i have ever found online is to restart the Agent, that seems to work for short term, but im getting 1-2 different boxes a day having the error.
Is there a better fix to this problem out there? any suggestions?
Cheers
s

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33506 More actions · Answer 1

Staggerlee (9/29/2014)
...We are finding a lot of the boxes are failing on the Agent Error Log, ....

Sorry, without you telling us the error message and other related information, there's little we can do to help you.

What is the error message?

Does it happen (always?) at specific instances?

Does it happen consistently (at interval, specific dates)?

Are there common settings across the instances where it happens?

.... any other information that could be relevant

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Staggerlee SSCertifiable Points: 5272 More actions · Answer 2

apologies,

so the error is :

Executed as user: svc-009. SQLServerAgent Error: 32. [SQLSTATE 42000] (Error 22022). The step failed.

All instances are configured the same, I cant find any pattern between when they happen, the problem is apparently widely known about, and there are lots of forum posts etc saying the fix is to restart the agent, to resolve the problem. Im just wondering if anyone has found a better solution or found any re-occurring patterns that cause it.

Example posts:

http://www.sqlservercentral.com/blogs/basits-sql-server-tips/2012/06/06/unable-to-recycle-sql-agent-log-sqlserveragent-error-32/

HanShi SSC-Dedicated Points: 33506 More actions · Answer 3

I can understand restarting the SQL Agent could be offered as a workaround, but it's not solving the root cause.

Can you check if the LOG-file is locked by anything. Can you check if there are starting/ending jobs (writing the log), snapshot or (file-)backup software running, anti-virus software running, etc. at that time?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

dbafromthecold@gmail.com SSChampion Points: 10160 More actions · Answer 4

I've seen jobs that check the SQL log for errors causing issues as they lock the files and prevent the cycle from happening.

In order to solve this I move the cycle job from 00:00:00 to 00:00:05 and this prevented the clash.

Also have you set the re-try option in the job? Set the retry interval to 1 minute and see if that allows the log to be cycled after an initial failure.

Staggerlee SSCertifiable Points: 5272 More actions · Answer 5

hey thanks,

Ive got a few other small jobs assigned to the same schedule. so will try moving it before then.

Cheers

s