Creating a Transaction Log Full Alert

When I was a kid, my mom had an old cookie-tin in which she kept her sewing supplies. On the lid was a sticker like this. I thought that was so clever. Anyway, we all have a list of things we say we’ll take care of when we get “a round tuit” (some longer than others), and I’m no exception. I have one database that has a semi-recurring problem of its index maintenance job failing due to a full transaction log. I’ve tried playing with the maitenance job schedule to find a less active time for it to run, but this particular database seems to be in a constant state of flux. When it fails I end up rerunning the index rebuilds/reorgs manually.

Needless to say, there are better uses for my time than rerunning index maintenance that failed over the weekend, so I finally decided to do something about it. I implemented a SQL Server Alert that will fire when the database’s transaction log gets over 50% full. When that happens, it will kick off a transaction log backup for that database. This should resolve the issue, we’ll see the next time we do index maintenance or a large load.

First, the job. It’s just a basic Agent job that runs the code below. The backup file is created with a date and timestamp appended to the filename.

DECLARE @LogBackupFile nvarchar(1000)

Set @LogBackupFile = ''F:\MyServer_backup\AdventureWorks\AdventureWorks_''+
 REPLACE(REPLACE(REPLACE(CONVERT(varchar(100), GETDATE(), 120),'':'',''''),'' '',''_''),''-'','''')+''.trn''

BACKUP LOG [AdventureWorks] TO  DISK = @LogBackupFile WITH NOFORMAT, NOINIT,
NAME = N''AdventureWorks-Transaction Log  Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Once the job was created, it was time to create the Alert. I’m creating a “SQL Server performance condition alert” which uses Perfmon counters to fire on the specified condition. I select the SQLServer:Databases object, the “Percent Log Used” counter for the AdventureWorks database. And I want the alert to fire when the percent used rises above 50%. That plus the incremental growth I allow for the log should be sufficient to prevent a full log.

On the Response tab, I select the log backup job I created earlier. I also want to know when this is happening, so I also check the Notify Operators box and opt for email notification.

And that’s it. Now it’s just a matter of waiting.

Also recommended: