I have a lot of servers and only home-grown monitoring. I needed a solution that would tell me if a log file started to grow outside of its normal size. I listened carefully at PASS Summit on how I should “right-size” my log files and came back with a plan to tackle all of them. Fast-forward a few months and I am still having trouble even after sizing them to what I thought they should be. One of the problems is that my backup solution is “touchy” with log backups and awesome at getting the full ones. I built a report to watch for missing transaction log backups, which isn’t perfect yet. I needed a more immediate solution and after getting another Disk Space Email Alert, I knew what I had to do. Here it is, my Large Log File Alert.
CREATE PROCEDURE [dbo].[usp_LargeLogFileAlert] @LogThreshold int = 1024 AS --This procedure sends out an alert message when a logfile has exceeded a set threshold. --It can be scheduled to run daily so you can act quickly to resolve. IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id = object_id(N'[tempdb]..[#LargeLogFileAlert]')) DROP TABLE #LargeLogFileAlert CREATE TABLE #LargeLogFileAlert ( DatabaseName VARCHAR(50) NOT NULL, LogMB INTEGER NOT NULL) DECLARE @SizeMB INT DECLARE @DatabaseName VARCHAR(50) DECLARE @AlertMessage VARCHAR(500) DECLARE @MailSubject VARCHAR(100) /* Populate #LargeLogFileAlert with data */ INSERT INTO #LargeLogFileAlert SELECT RTRIM(instance_name) AS DatabaseName, cntr_value/1024 AS SizeMB FROM sys.dm_os_performance_counters WHERE counter_name = 'Log File(s) Used Size (KB)' AND instance_name != '_Total' AND cntr_value/1024 &gt; @LogThreshold --Threshold in MB DECLARE LogAlert CURSOR FAST_FORWARD FOR SELECT DatabaseName, LogMB FROM #LargeLogFileAlert OPEN LogAlert FETCH NEXT FROM LogAlert INTO @DatabaseName, @SizeMB WHILE (@@FETCH_STATUS = 0) BEGIN SET @MailSubject = 'Log for ' + @DatabaseName + ' has exceeded threshold on ' + @@SERVERNAME SET @AlertMessage = @DatabaseName + ' is ' + cast(@SizeMB AS VARCHAR) + ' MB. Please verify transaction log backups are working.' -- Send out email EXEC msdb..sp_send_dbmail @recipients = 'firstname.lastname@example.org',--Change to your email @subject = @MailSubject, @body = @AlertMessage FETCH NEXT FROM LogAlert INTO @DatabaseName, @SizeMB END CLOSE LogAlert DEALLOCATE LogAlert DROP TABLE #LargeLogFileAlert GO
The threshold is adjustable by server. I picked the one I did because my servers have a lot of small databases.
Next I tossed it into a SQL Agent Job and have it run once a day on each server. It is light so I am not worried about it causing performance issues. It will email me for each database that has a log file larger than the limit I set. It won’t email at all if there aren’t any. Once I get my logs sized properly, I can turn them off. I used my CMS to deploy the stored procedure and SQL Agent job to all my servers and Yay, I know more about my environment.