Performance Condition Alerts: Warnings Before a Log File Fills

Error 9002 is a late way to learn that a transaction log is full. Performance condition alerts can warn you while there is still room to act. The useful alert names the database, fires at a measured threshold, and reaches someone who can respond.

Performance Condition Alerts That Leave Time to Act

Percent Log Used is a SQL Server Databases performance counter. Free space in tempdb (KB) belongs to the Transactions object. Processes blocked belongs to General Statistics. These counters have different meanings. A log at 80 percent on a tiny file can be urgent, while the same percentage on a well-sized file can leave more room. Tempdb free space is an absolute count of free kilobytes, so choose a threshold from actual growth and response time.

I check recent peak usage and the time needed to expand, back up, or clear the condition. The alert should fire before the final margin disappears. It should not fire every time a scheduled load uses its expected space. What action can the operator take before the threshold becomes an outage?

Confirm the Counters on This Instance

Object names differ between a default instance and a named instance. Query the counters on the target server and copy the exact object name. This also confirms the counter exists on that build. Do not paste a default-instance object name into a named-instance script and assume Agent will translate it. On my named test instance, sp_add_alert accepted the default-instance prefix without any error, so a clean call proves nothing.

SELECT DISTINCT object_name, counter_name, instance_name
FROM sys.dm_os_performance_counters
WHERE counter_name IN
  (N'Percent Log Used', N'Free space in tempdb (KB)',
   N'Processes blocked')
ORDER BY counter_name, object_name, instance_name;

For Percent Log Used, each database has an instance row. The alert condition must name the intended database. For the other two, the instance field is normally blank. Read the values during a normal busy period too, so your first threshold comes from this server rather than a slide deck.

Create a Response Job Before Linking Alerts

A response job should collect evidence and notify the on-call operator. It should not automatically shrink a log, kill a blocker, or change file settings. Those actions can worsen the incident. Start with a read-only snapshot: database log usage, tempdb space, active requests, and the time. Retain Agent job output so the alert can be investigated after the counter falls again.

USE msdb;
EXEC dbo.sp_add_job
    @job_name = N'DBA Alert Snapshot',
    @enabled = 1,
    @description = N'Collect read-only context for performance alerts';
EXEC dbo.sp_add_jobstep
    @job_name = N'DBA Alert Snapshot',
    @step_name = N'Capture log usage',
    @subsystem = N'TSQL',
    @database_name = N'master',
    @command = N'DBCC SQLPERF (LOGSPACE);';
EXEC dbo.sp_add_jobserver
    @job_name = N'DBA Alert Snapshot';

This is a minimal starting job. In production, add durable output and the other context queries. Configure Database Mail and a tested SQL Agent operator separately. If the job already exists, review and update it instead of running CREATE again. The job itself needs a failure notification, because an alert that launches a broken job is just a quiet alarm clock.

Add the Log Usage Alert

The performance condition string is object, counter, instance, comparison, and value, separated by vertical bars. Use the exact object name from the discovery query. The example targets a default instance and a database called YourDatabase. Replace both. The 80 percent threshold and ten-minute response delay are examples to tune from the database's real size and recovery procedure.

USE msdb;
EXEC dbo.sp_add_alert
    @name = N'YourDatabase log usage warning',
    @enabled = 1,
    @delay_between_responses = 600,
    @performance_condition =
      N'SQLServer:Databases|Percent Log Used|YourDatabase|>|80',
    @job_name = N'DBA Alert Snapshot';

A log can be full because a long transaction, unavailable log backup, replication, availability replica, or other reuse hold prevents truncation. The response job should help identify the reason. Do not assume the fix is a bigger file, and do not automatically change the recovery model. Leave enough delay to avoid repeated pages for one event, while keeping a separate escalation path if the condition stays high.

Cover Tempdb and Blocking With Their Own Limits

Tempdb free-space alerts use a falls-below comparison. Blocking uses a rises-above comparison. Set their delays and thresholds independently. A few short blocked requests can be routine; a sustained queue during a business-critical period is different. The Agent performance monitor samples periodically, so an alert is not a millisecond-level detector.

USE msdb;
EXEC dbo.sp_add_alert
    @name = N'tempdb free space warning',
    @delay_between_responses = 600,
    @performance_condition =
      N'SQLServer:Transactions|Free space in tempdb (KB)||<|1048576',
    @job_name = N'DBA Alert Snapshot';
EXEC dbo.sp_add_alert
    @name = N'Blocked processes warning',
    @delay_between_responses = 300,
    @performance_condition =
      N'SQLServer:General Statistics|Processes blocked||>|5',
    @job_name = N'DBA Alert Snapshot';

The free-space example warns below 1 GB. That value is not safe for every instance. Check tempdb size, growth, and typical sort or version-store bursts. The blocked-process example warns above five sessions, but it does not tell you how long each has waited. The response job should capture blocking chains before they disappear.

Route Performance Condition Alerts to a Real Operator

Create and test a SQL Agent operator with a monitored email address, then attach it to each alert through sp_add_notification. The operator name in the example must already exist. Send a test through Database Mail and Agent, then verify delivery. A configured address that nobody watches does not count as monitoring.

USE msdb;
EXEC dbo.sp_add_notification
    @alert_name = N'YourDatabase log usage warning',
    @operator_name = N'DBA On Call',
    @notification_method = 1;

Repeat for the other two alerts after validating the first. SQL Agent must be running, the alert must be enabled, and the performance condition must match a real counter. Review msdb alert history after a controlled threshold test. A response delay limits repeated actions; it does not guarantee one email for an entire incident. Document who owns the alert and how to silence it during planned maintenance.

For a named instance, copy the exact performance object prefix returned by the discovery query into each condition. Also test the response job under the SQL Agent service identity. It needs permission to read the diagnostic views and write its output, yet it should not need rights to change the affected database. An alert that fires but cannot collect evidence leaves the operator with the original problem and a second mystery.

Review Performance Condition Alerts for Noise and Misses

After two weeks, compare alerts with actual incidents. Lower thresholds if the team had too little time to respond. Raise or redesign noisy thresholds that fired during expected work. Keep the counter value, time, database, response job result, and operator acknowledgment. I would rather adjust an alert with evidence than let it train the team to ignore the next page.

A performance condition alert is one layer. Continue to monitor file growth, backup health, and log reuse waits. A healthy response path means the person who receives the message can see why the number moved and what to do next. Warning before 9002 is the goal; avoiding a parade of unactionable emails is part of the same job.

Related reading on this blog: SQL Server Alert Management: From Chaos to Clarity and SQL Agent Alerts Every Instance Should Have.

An alert is not protection by itself, it is a prompt for a ready response before the file fills.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.