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.
| 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 strExcludeList = "TEMPDB/PUBS/NORTHWIND/MODEL" 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 mSQLServer.Connect '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 Else 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 Next Next mSQLServer.Disconnect Set mSQLServer = Nothing
|
