WikiDBA

For a Database Administrator managing Log Files growth is a consequential task and I hope every DBA have been faced this issue on very frequent basis. Depending on coding pattern and business logic implementation there might be n numbers of causes for Log files growth. Now a DBA wanted to get notify when a defined threshold regarding file growth breaches, a system generated alert should be triggered and the related team members should notify via email.

Here, to do this, we can create an alert in SSMS as per below steps for desired notification as,

Step 1) Right click on “Alert” from SQL Server Agent Section and select “New Alert”

Step 2)     From General Page, we can define parameter as below

a) Define Name as per your convenience , I have given Name as LogFileSize5GB, because here I want alert when Log file size increase after 5 GB in size.
b) Select Type as SQL Server Performance condition alert
    c) Just Set parameter in Performance condition alert definition section as
Object = Databases
Counter = Log File(s) Size (KB) —- 5242880 KB = 5 GB
Instance = Database Name which log files growth needs to be monitored, in this case I have select my test DB – VirendraTest

Step 3)     Click on Response Page, we can define parameter as below
Note : We need to create operator(s) where we can specify to whom we wanted to send emails.

Step 4)     Click on Options Page, we can define parameter as below

Step 5) Click on OK, an alert has been configured and whenever log file size increases more than 5 GB, respected team members will get notified.