Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Cleveland DBA

Colleen Morrow is a SQL Server Consultant at UpSearch, a provider of DBA resources in northeastern Ohio. She has worked in the IT industry for 20 years, doing everything from technical support to development to database administration on Informix, Oracle, and SQL Server platforms.

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

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.


Leave a comment on the original post [, opens in a new window]

Loading comments...