up vote -6 down vote favorite
share [g+] share [fb]

I want to monitor the transaction logs of a database and send some kind of alert once a threshold is reached.

How can I do this?

link|improve this question
2  
Is there a question in there? – MDMarra Jan 29 at 21:07
@Fhd.ashraf You'll need to be a lot more specific than that and as MDMarra says actually phrase it in the form of a question. Try searching the site for other monitoring solutions; plenty have been mentioned previously. – James O'Gorman Jan 29 at 21:14
wow..let me go back to elementary school.. the log files get over sized in a SQL , and if it exceeds a certain size, send an email to my email !! i need like a job or SQL Agent scheduled task ..any help ! ? – Fhd.ashraf Jan 29 at 21:16
Are you talking about transaction logs or a log file? – James O'Gorman Jan 29 at 21:21
Transaction Log bro – Fhd.ashraf Jan 29 at 21:25
feedback

1 Answer

up vote 1 down vote accepted

The MS SQL DBA approach:

Take a look at this tutorial about how to send mail from SQL server 2008 and configure your server accordingly.

Use the DBCC SQLPERF(logspace) function to retreive the data for your transaction log size and usage and run sp_send_dbmail to send the alert to your desired mail address. Schedule using the SQL agent or via a scheduled task calling sqlcmd to run periodically.

The sysadmin approach:

Use your favorite / currently employed monitoring system to watch the file / folder size and set thresholds for alerts.

The proper approach:

Do not let your transaction log files grow excessively in the first place. Database or log backups will truncate the log upon completion, so run them regularly. Or change to the simple recovery model if you do not need point-in-time restores.

link|improve this answer
Hmm... the "proper approach" could've been less snarky. Sometimes, someone leaves a transaction open and then the log doesn't clear after a log backup. So having an alert is still a good idea. – Ben Thul Jan 30 at 3:52
@BenThul I've added it as an afterthought, as the problem of databases simply not taken care of and either not backed up (since they are deemed unimportant) or backed up through a VSS copy without truncating the logs (since this is a common and simple approach) is significantly more common than the one of large or indefinitely open transactions. – syneticon-dj Jan 30 at 7:31
feedback

Your Answer

 
or
required, but never shown
discard

Not the answer you're looking for? Browse other questions tagged or ask your own question.