How to configure an alert for low log file space for a specific database

Yesterday, I have received an error notification from our network team stating that we are running out of disk space on one of our SQL Servers data drive. When I logged into the server I noticed one of our database’s Transaction Log grew tremendously. I know my main focus should be the cause of this growth but at the same time I needed to setup an alert for those kinds of situations to notify the DBA team before I receive an email from our network team saying that there is no disk space on a drive. Also I definitely do not want to monitor the T-Log files manually on a scheduled basis.

Here is how I setup an alert for this purpose:

    1. In SQL Server Management Studio, under SQL Server Agent, right-click Alerts and select New Alert.
    2. Give your alert a name e.g. “Low Log Space Alert – DbName”
    3. From the Type drop-down list, select “SQL Server Performance Condition Alert”.
    4. From the Object drop-down list, select the “Databases”.
    5. From the Counter drop-down list, select “Percent Log Used”.
    6. Select your database (DbName) from the Instance drop-down list
    7. Select Rises Above from the Alert If Counter drop-down list and enter “95” into the Value text box to set the alert for to receive notification when the log rises above 95
    8. From the left side of the New Alert window, select the Response option
    9. Select the Notify Operators check box, and if the operator is not listed on the list, then click on a “new Operator” to add a new operator and fill out the details for it. If the operator exists on the list, then select the check box for your operator.
    10. Select the Options page and select the E-mail check box to include the alert error text.
    11. Click OK to finish.

If you prefer not to use wizard, here’s a T-SQL version how to send automated email alerts whenever a SQL Server Monitoring Transaction Log size exceeds a specified threshold.

-- step_1: Declare threshold percentage limit
DECLARE @threshold   INT = 5
-- step_2: Create temp table and insert sqlperf data into it
CREATE TABLE #tlogtables
   databaseName   sysname,
   logSize        DECIMAL (18, 5),
   logUsed        DECIMAL (18, 5),
   status         INT
INSERT INTO #tlogtables
-- step_3: get T-logs exceeding threshold size for a specific database
SELECT databaseName,
  FROM #tlogtables
 WHERE logUsed >= (@threshold) AND databaseName = 'DbName'
-- step_4: send email if a T-log exceeds threshold
IF OBJECT_ID ('tempdb..#tlogtables') IS NOT NULL
      EXEC msdb.dbo.Sp_send_dbmail @profile_name = 'Your database mail profile',
                                   @body         = 'Body of your email',
                                   @recipients   = 'recipents email address',
                                   @subject      = 'ALERT: ... ';
DROP TABLE #tlogtables

The above T-SQL is very simple. The T-Log size across all database is inserted into a temp table by executing DBCC SQLPERF(LOGSPACE). Although there are other ways such as querying  sys.database_files to achieve this calculation, for this example I used DBCC SQLPERF(LOGSPACE).

The temp table is then queried to get a specific database with T-Log greater than the specified threshold (@threshold parameter).

The result mail is sent using database mail stored procedures. Let us know if you need help setting up other automated emails for your SQL Server.

0 0 votes
Article Rating
Notify of
Inline Feedbacks
View all comments
Would love your thoughts, please comment.x