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?
Asked
Active
Viewed 5,207 times
-6
-
2Is there a question in there? – MDMarra Jan 29 '12 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 '12 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 '12 at 21:16
-
Are you talking about transaction logs or a log file? – James O'Gorman Jan 29 '12 at 21:21
-
Transaction Log bro – Fhd.ashraf Jan 29 '12 at 21:25
1 Answers
1
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.
the-wabbit
- 40,737
- 13
- 111
- 174
-
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 '12 at 03: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. – the-wabbit Jan 30 '12 at 07:31