How Often To Backup Log Files

[gharabed]

I am fairly new to SQL Server but have a lot of experience with Oracle. I am trying to set up a backup plan for a transaction processing based SQL Server database. With Oracle, the transaction logs can automatically be archived (backed up). Apparently with SQL Server, the transaction logs are archived via the backup/maintenance plan and are cleared out when a backup is performed. However, if I restrict the size of the transaction log file, is it purely a matter of knowing how much transaction space is used by my database to determine how often the backup of the transaction logs has to take place? This seems sort of strange to me because you could have an anomolous large transaction that could cause your database to come to a screeching halt. Also, how do you tell how large your transaction logs should be? Simply by allowing them to grow for a period of time and seeing how large they get and then limiting them to that size? Seems kind of a strange way to do it. Any input on this is appreciated.

 

[tlbroadbent]

I wouldn't setup a transaction log backup plan based on the log size. You are correct in noting that one large transaction could cause problems with a backup plan based solely on log size.

We have determined the transaction backup interval based on how much data loss is acceptable in case of a catastrophic failure of the server. In most cases, we backup transaction logs every 30 minutes throughout the day. Some database logs are backed up less frequently and some require no log backups because the data can be reloaded from other sources.

It is important to determine your backup and recovery needs and develop a maintenance plan in accordance with your needs. There are several resources to help in the development of a backup/restore or disaster recovery plan. See faq183-1784.

If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[fluteplr]

I totally agree with Terry. Also why do you feel you need to limit the transaction log size? I usually just let sql server manage them and keep an eye on the servers to ensure I have suficient disk space available.

 

[gharabed]

In reality I will probably not limit the size of the transaction log but it seem sort of strange to me to allow a file to grow unchecked. I would think a better architecture would be to copy the log file off when it fills (worst case) as opposed to bringing down the whole database and telling the calling process that the transaction log is full. Guess I have to take that up with Microsoft.

 

[cchitanu]

Try these in Query Analyzer :

To find the used size of the log file :
1. dbcc sqlperf (logspace)

To backup the log
1. backup log db_name to db_name_log with init (or noinit)
- with init will override the old backup log file
- with noinit will append to the old backup log file

To truncate the transaction log file :
1. backup log db_name to db_name_log
2. dbcc shrinkfile(db_log_name,new_size)

Hope this helps
Cristian