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. 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.

SET NOCOUNT ON
-- 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
EXECUTE ('DBCC SQLPERF(LOGSPACE)')
-- step_3: get T-logs exceeding threshold size for a specific database
SELECT databaseName,
       logSize,
       logUsed,
       status
  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
   BEGIN
      EXEC msdb.dbo.Sp_send_dbmail @profile_name = 'Your database mail profile',
                                   @body         = 'Body of your email',
                                   @recipients   = 'recipents email address',
                                   @subject      = 'ALERT: ... ';
   END
DROP TABLE #tlogtables
SET NOCOUNT OFF

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.