3

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

Improve this question
1
  • (While this has nothing to do with your question...) You probably need to backup your transaction log more often than every 3 hours. What's the acceptable amount of data loss in the event of a system failure? If you've got a database which is 500 Gigs in size I'm guessing that it's pretty important to the business and probably can't afford to loose 3 hours worth of data.
    – mrdenny
    Commented Apr 20, 2012 at 18:32

1 Answer 1

Reset to default
2

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.

Improve this answer
1
  • Whoops, I didn't see that in the Alerts dialog. Thanks ;-)
    – Allen Jantzen
    Commented Apr 20, 2012 at 14:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.