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:


Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

4 thoughts on “Creating a Transaction Log Full Alert

  • Daniel Rosales

    This is really helpful and the first post I’ve seen talking about this particular issue.

    I have a question: What happens if the database or databases are already in “simple” mode? I understand that when in simple mode, you can’t backup the transaction log.

    Do we have to run the index maintenance without changing databases mode to simple? We do this before starting the index maintenance job to avoid getting the transaction log full. What do you recommend in this case? Thank you very much for the information.

    • abdallah

      Bonjour Daniel Rosales

      Si votre base de donne est en mode récupération Simple il existe un événement normée checkpoint qui permet de vider votre Log et fait un point de terminaison
      dans cette cas votre Log serai valider a chaque point du terminaison il est configurer par défaut d’être lancer chaque 5s

      merci