March 11, 2008 at 7:29 am
How can I make and alert and send via mail that the transaction log has reached a certain size?
March 11, 2008 at 7:39 am
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
March 11, 2008 at 8:14 am
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...
March 11, 2008 at 8:18 am
That is incorrect - look at the alerts again - change the type and you have a whole new set of conditions...
-- Cory
March 11, 2008 at 8:24 am
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
March 11, 2008 at 8:28 am
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?
March 11, 2008 at 8:33 am
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
March 11, 2008 at 8:36 am
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