Get an alert/email when the logfile of a database is full

Question

Get an alert/email when the logfile of a database is full

AAkula

SSCertifiable

Points: 5394
More actions
February 4, 2009 at 8:38 am

#127281

hello,
I would like to get an alert/email when the log file of a database is full. How can I achieve this?
thanks

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

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

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 1

This can be achieved in a simple way by setting up alerts..

Go to Management Studio-->SQL Server Agent-->Alerts--> New Alert--> Give whatever name you want and specify severity 17 ( which is insufficient resources) and you can add filter by checking on Raise alert when message contains The log file for database 'DBName' is full. Back up the transaction log for the database to free up some log space...

In the response tab on the left you can say 'Notify operator' which has to be created as an email recipient or pager or netsend with a custom message that you want. Also you can set additional options as you want.

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

AAkula SSCertifiable Points: 5394 More actions · Answer 2

Thanks..

I did what you have suggested as below. and I intentionally filled up the E drive where the log files of all the databases reside. But I did not get an alert/email?

could you suggest me where Iam doing wrong

Go to Management Studio-->SQL Server Agent-->Alerts--> New Alert--> Give whatever name you want and specify severity 17 ( which is insufficient resources) and you can add filter by checking on Raise alert when message contains The log file for database 'DBName' is full. Back up the transaction log for the database to free up some log space...
In the response tab on the left you can say 'Notify operator' which has to be created as an email recipient or pager or netsend with a custom message that you want. Also you can set additional options as you want

.

Thanks!!

MVIR Valued Member Points: 63 More actions · Answer 3

As per my understanding I think what you did was filling the space on the operating system's drive but the log file still might have some empty space in it.So it is not full and does not send you an alert email!!!

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 4

kotlas7 (2/4/2009)
Thanks..
I did what you have suggested as below. and I intentionally filled up the E drive where the log files of all the databases reside. But I did not get an alert/email?

With that do you mean your log file grew to the size of disk?

Instead you can set a max size for the log file and uncheck 'Automatically grow file'. If the file grows beyond your set size limit it will throw errors and hence alerts.

Make sure you have configured the operator correctly, for it to work.

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."