Emailing: Transaction Log size

  • How can I make and alert and send via mail that the transaction log has reached a certain size?

  • look under "SQL Server Agent" under Alerts - in there you can make an alert for quite a number of things.

    Here is an example of some TSQL for that...

    USE [msdb]

    GO

    EXEC msdb.dbo.sp_add_alert @name=N'tlog size',

    @enabled=1,

    @delay_between_responses=0,

    @include_event_description_in=0,

    @performance_condition=N'SQLServer:Databases|Percent Log Used|_Total|>|75'

    GO

    EXEC msdb.dbo.sp_add_notification @alert_name=N'tlog size', @operator_name=N'OperatorName', @notification_method = 1

    GO

    -- Cory

  • Well i know thats in Alerts, but i can only choose the Error Number and what the message should contain. But i have no clue which number to enter or what the message should contain...

    ... and its not how much of the space for the log is used- i want to know when the Transaction Log exceeds 5Gb or so...

  • That is incorrect - look at the alerts again - change the type and you have a whole new set of conditions...

    -- Cory

  • vprunic (3/11/2008)


    ... and its not how much of the space for the log is used- i want to know when the Transaction Log exceeds 5Gb or so...

    Once you explore the GUI more, you will see there are loads you can do, and that my example was just that, an example...here is something more along what you are asking for, which, btw, was not explained in the original post:

    USE [msdb]

    GO

    EXEC msdb.dbo.sp_add_alert @name=N'd',

    @enabled=1,

    @delay_between_responses=0,

    @include_event_description_in=0,

    @performance_condition=N'SQLServer:Databases|Log File(s) Used Size (KB)|_Total|>|5242880'

    GO

    EXEC msdb.dbo.sp_add_notification @alert_name=N'AlertName', @operator_name=N'OperatorName', @notification_method = 1

    GO

    And before you get get to worried, the _Total can be for a specific DB as well, it is all very easy to figure out via the GUI, its okay, give it a try! 🙂

    -- Cory

  • This did the trick. Thanx

    And 2 more questions- if its not a prob....

    If i want to get an alert if ANY of the Transaction logs exceed the size, do i have to make an alert for every one? Or is there another way around it?

    I guess that for _total it will calculate the entire size of All the logs, or?

  • I am not sure on this, but, it seems to me that _Total would be the total of all that is being watched. If you need this at a DB level, I suspect you will need to make one for each db. This should not be that difficult as you can script the alert and make some minor changes for each DB.

    -- Cory

  • Once again- thanx. You helped me out a lot.

    Not that hard when you know where to look:cool:

Viewing 8 posts - 1 through 7 (of 7 total)

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