Minimizing the Number of Log Backups


Do you do transaction log backups of your databases? Of those databases, how many are really 

active on any given day? Wouldn't it be nice to only do log backups on the databases being used, 

instead of just doing all of them? Why not just do all of them? To reduce server workload. It's 

NEVER a bad thing to look for ways to reduce the workload!

My work situation is perhaps a little unusual, but I bet not unique. I have 170 databases that 

store client data. On any given day 50-60 of them are active - meaning at least one transaction 

is processed. If I do a log backup on each database every 15 mins between 8 am and 5 pm, I will 

have...9 hrs x 4 logs per hour x 170 dbs = 6120 backup files!

6120 files! You could reduce that number by using a device instead of a file, but still, that's 

the amount of work you're doing.

Compare that with only doing a backup of the 60 active databases at the same interval results in 

2160 files. I haven't given up my ability to recover to 15 mins, but I do have about 4000 fewer 

files - not to mention the decreased disk and processor load. Of course you're probably ahead of 

me here - how the heck do you only back up the active databases without it becoming a hands on 

admin task?

There are two ways. The first is to log each time someone uses a database (using Profiler), then 

do a query to return the databases being used to determine which databases to back up. This 

works, but you could end up doing backups of databases in which the only thing a user did is run 

a select query. You can even tweak this a bit to only do a backup on databases that have had a 

login in the past backup interval or two, further reducing the number of backups you will have to 

do. It's a good technique and if you're already auditing usage you can probably implement it 


The other method is a bit of a hack! For this one you just check the size of the log file and 

when it grows beyond a certain size, you do the transaction log backup followed by a shrink log. 

I like this method for a couple reasons. One is that it keeps the size of the log files under 

control. The other is that it's really usage based. If all that is happening are select queries 

then there will be no log activity and no reason for me to do a backup. You can control the 

frequency of the backups by just adjusting the log file size cutoff - drop it to 10-15 megs to 

capture even a small number of transactions, or raise it to 100 megs or higher if you're in a 

high transaction environment.

I'm including some sample DMO code that you use as the starting point to test this idea. You'll 

need to modify the sBakPath to point to the folder where you store your backups. You should also 

modify the two lines that set the strDB variable so that a unique file name is created each time. 

I like to use a combination of date and time, but you can use any technique you like.

If you have questions or comments, please post them in the discussion area under DMO. I usually 

check in there once or twice a day during the week.

Dim mSQLServer

Dim mDatabase

Dim mBackup

Dim strDB

Dim strMessage

Dim strExcludeList

Dim sBakPath

Dim oLogFile

On Error Resume Next

'NOTE: Change this to wherever you keep your backups!

sBakPath = "C:\backup"

'No need (usually!) to worry about transaction logs for these 4, you may

'want to add others


Print "The following db's were excluded from the transaction log backup:

" & strExcludeList

'connect using a trusted connection

Set mSQLServer = CreateObject("SQLDMO.SQLServer")

mSQLServer.LoginSecure = True


'now just cycle through each db, only backing up the transaction log

'if the file size is more than 50 megs (arbitrary, pick a number that

'works for you). 

For Each mDatabase In mSQLServer.Databases

For Each oLogFile In mDatabase.TransactionLog.LogFiles

If oLogFile.Size >= 50 Then

If InStr(strExcludeList, UCase(mDatabase.Name)) = 0 Then

Set mBackup = CreateObject("SQLDMO.Backup")

mBackup.Database = mDatabase.Name

strDB = ""

If UCase(mDatabase.Name) = "MASTER" Then

strDB = sBakPath & "\FULL_" & UCase(mDatabase.Name) & ".bak"

mBackup.Files = strDB

mBackup.Action = 0


If mDatabase.DBOption.TruncateLogOnCheckpoint <> 0 Then

mDatabase.DBOption.TruncateLogOnCheckpoint = 0

End If

strDB = sBakPath & "\LOG_" & UCase(mDatabase.Name) & ".bak.

mBackup.Files = strDB

mBackup.Action = 3

End If

If strDB <> "" Then

mBackup.SQLBackup mSQLServer

End If

Set mBackup = Nothing

oLogFile.Shrink 10, 0

End If

End If




Set mSQLServer = Nothing