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 
easily.

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.