Log Backups, Auto Growth Settings & Alerts (Part 2 – Missing Log Backup Alert)

In the previous post we looked at how log backups and auto growth settings can affect performance. Now let’s look at some alerts for when things start to go wrong.

Missing Log Backup Alert

The primary reason most transaction logs start to (unexpectedly) grow is that the transaction log hasn’t been backed up. This is usually caused by one of two situations: the log backup job failed, or the log backup job didn’t start. Most options for backing up SQL transaction logs (3rd party software, SQL Agent job, or maintenance plan) will include an alert/notification mechanism for when the job fails. Where some backup options are lacking is in notifying that a (log) backup that should have been taken, wasn’t. If the backup (or management) software doesn’t include an alert for missing backups, SQL Server (2008 & newer) has a solution. Policy Based Management (PBM) includes a policy (Last Log Backup Date) that will send an email notification when the last backup date/time exceeds a specified threshold.

The policy is easy to implement using SQL Server Management Studio (SSMS).

1. Open SSMS and connect to the desired SQL Server.
2. Expand the Management folder and the Policy Management subfolder.
3. Right-click on Policies and select Import Policy…
4. Click the … (3 dots) button beside the ‘Files to Import’ text box.
5. Browse to the C:\Program Files (x86)\Microsoft SQL Server\<SQL Compatibility Level (ie. 140)>\Tools\Policies\DatabaseEngine\1033\ folder.
6. Select the Last Log Backup Date xml file and click OK to complete the import.
7. The policy will appear in the SSMS Policies folder.
8. If the policy is disabled (red X beside it), Right-click on the policy and select Enable.
9. The default policy’s Properties can be altered to fine-tune it’s operation:
   1. Check Conditions – used to specify the date/time threshold for triggering an alert.
   2. Targets – used to specify which databases are subject to the policy.
   3. Evaluation Mode – Manual execution (on demand) or schedule based.

In Part 3 we’ll look at an alert for when the disk(s) are running low on free space.