I have a SQL Server 2005 database thats roughly half a terabyte. Third party software that I don't control writes to the database.
I have a Maintenance Plan which backs up the transaction log, and is called by an Agent Job which executes every 3 hours. The Agent job as a second step also compresses the finished trans log backup with gzip. Sometimes there are sporadic large bursts of updates to the database, and the transaction log starts to fill prematurely.
I created an Alert on the database based on the SQLServer.databases object, Percent Log Used counter, that responds by executing my backup trans log Agent job when percent of log used goes above 75%.
When I enabled the alert and a burst of updates occurred, the Alert fired much more quickly than the job could backup and compress the trans log, about 1 alert every 3 seconds I think. I think a bunch of job start/exec commands were queued up, and weird things began to happen with the job – it would sit there spinning and never finish. Job Activity Monitor said the gzip of a trans log was happening, but NO files were being compressed..
I finally had to stop the job and restart Agent.
So it looks like the Alert fires too quickly for the job. What can I do about this? Can I somehow set some flag (maybe a file?) while the job is working, and the alert does not call the job when the flag is set?
Am I just missing something in how this should work?
Any help appreciated.
aj
Best Answer
In the properties of the Alert you can set a "Delay between responses". You can set the delay to slightly longer than it takes to do the compression.