• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 633
  • Last Modified:

Transaction log backups - best practices?

I am the administrator for a CAD Document Management System running on SQL Server 2000. I am having issues where my DBA group insists it is their policy NOT to let transaction logs grow, and they do not have them backed up and truncated as part of a database maintenance plan.

My system undergoes thousands of transactions per day. My data file is currently over 2GB. The DBA group has set my transaction log max size at 100MB (they just grudgingly increased it from 50MB). As such, I am continually experiencing issues where the system cannot complete transactions because the log file is full.

Yet, since I am not in the DBA group, the refuse to make an exception to their policy. Can anyone point me to any best practices that might offer me some assistance?
0
MPalicki
Asked:
MPalicki
  • 3
  • 3
  • 2
  • +1
2 Solutions
 
rafranciscoCommented:
Is setting the Recovery Mode to Simple an option for you?
0
 
MPalickiAuthor Commented:
Really don't know - I've never had to do a recovery, and I don't know what their policy is for setting recovery. (I'm pretty familiar with SQL Server and was a DBA in a past life, but this is a larger organization so I'm just the CAD monkey.)
0
 
rafranciscoCommented:
The easiest solution for you is to set your Recovery Mode to Simple since your log has never been backed up before anyway.  You can ask your DBA to do this for you.  This will solve your problem of having your log file growing big.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Scott PletcherSenior DBACommented:
>> and they do not have them backed up and truncated as part of a database maintenance plan.  <<

Do they back them up and truncate them some other way?

They cannot continue to let the log grow without ever directly reducing it, especially since they have so limited its total size.  Ultra-simple rules don't always work in the real world, however much some people would like them to.


Small transactions should not be a problem.  However, if you ever had to a single large/very large statement, such as an UPDATE, it would have to be logged as *one transaction*: if that one transaction reaches 100MB (at most) of log space, it *will* abend due to lack of log space.  Furthermore, the server may even have to stop *since the backout process that follows the abend will also have to be logged*.

I think you should document all this and present it to your manager (first), review it, then pass it along to the DBA group.  Then, if something happens later, at least you have some "cover".
0
 
Scott PletcherSenior DBACommented:
Just realize that setting your recovery mode to "Simple" means that if *any* serious error occurs, you must fall back to the last backup.  *All* activity since then is completely lost.  For most business that is unacceptable, but your particular case might be an exception.  It is still folly for a business to allow such arbitrary and simplistic rules to prevent what really needs done from happening.  

Perhaps you need to replace a DBA or two? :-)
0
 
MPalickiAuthor Commented:
Thanks - at least I know that I'm not insane and my request is somewhat reasonable. Is there at least any documented best practice you know of regarding what size the log file should be in relation to the data file?

(and believe me - the replacement idea has crossed my mind)
0
 
pcsentinelCommented:
This really isn't right.
Unless the DBA group has a specific reason for this then it makes no sense. The whole point of a transaction log is to allow recovery int the event of a failure.

Now to put this into perspective as rafrancisco says can you use a simple recovery model. Can you afford to loose data. i.e. is you data recoverable from base information and if so can it be done within an acceptable time period.

How often is the transaction log being backed up? And what is your longest running transaction?

It may be that the log is being backed up regularly during the day but because you have a long running transaction then it cant be shrunk.

I have seen badly manged systems running to Gigabytes of log space with only a 50Mb of dataspace. But if backups are being done properly then this shouldn't be happening.

Finally it seems to me that the DBA's are setting arbitrary limits where they should be looking at individual cases and really its up to the DBA's to manage the log file sizes not you.

Gets my goat really

regards


0
 
rafranciscoCommented:
>> what size the log file should be in relation to the data file? <<

The recommended size of the log file is 25% of the data file.  According to Books Online, in the CREATE DATABASE part,

If LOG ON is not specified, a single log file is automatically created with a system-generated name and a size that is 25 percent of the sum of the sizes of all the data files for the database.
0
 
Scott PletcherSenior DBACommented:
To me that's just as silly as any other arbitrary amount.  Yes, you have to start somewhere, but after that it should be based on the db and its application(s), what they need, not on some arbitrarily mandated percentage.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now