Post reply

Prevent Alert From Triggering

  • September 28, 2007 at 2:13 am

    #120622

    I have created an alert that triggers when the transaction log reaches a certain file size. The alert runs a job that backs up the log and then shrinks the file.

    However, I noticed that the alert was also triggering during the overnight backup, maintenance routines. When the alert tries to execute it cannot lock the transaction log to either backup or shrink. This means that every morning I have a lot of emails telling me that the alert failed.

    Is there anyway that I can stop the alert from executing during a certain time frame i.e. whilst the maintenance, backup tasks are being executed?

    Thanks

  • September 28, 2007 at 2:46 am

    #737039

    I'd actually change it to not have to shrink the log file - have you checked your virtual log device numbers or how much ntfs fragmentation you have caused?

    Failing that review your maint routines not to bloat the log - a transaction log is best set at a static size.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • September 28, 2007 at 2:56 am

    #737042

    David,

    to disable your alert run:

    EXEC dbo.sp_update_alert

    @name = N'Test Alert',

    @enabled = 0 ;

    You could run this before your maintenance jobs start and re-enable it afterwards.

    But I wonder if your alert really is such a good idea. If it happens so often that the alert gets triggered, maybe you should go for a larger file size. Also it's not recommended to shrink your transaction logs, unless you're really running out of diskspace.

    Markus

    [font="Verdana"]Markus Bohse[/font]

  • September 30, 2007 at 12:40 pm

    #737550

    Hi Colin/Markus,

    Thanks for your replies. I'm not really sure what to do because I have a partition of about 40Gb that is supposed to hold the backup file plus TLog file. Currently the backup is around 21Gb and the TLog has reached 14Gb before. When this is added to the other system/application files on that partition it soon runs out of disk space.

    I do have a lot more capacity on the partition that contains the data files but I was trying to keep the data files and backup files seperate in case of failure.

    Any suggestions other than increasing the capacity on the partition?!

    Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

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