How big should my Transaction Log File be?

Last Post 18 Mar 2008 12:35 PM by Jack_Kaye. 6 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Jack_Kaye
New Member
New Member

--
18 Mar 2008 09:30 AM
I'm currently running the Siebel application that is SQL2000 based. I'm curious to know if the transaction log should be as large as the database file. Presently Siebel is running at about 13GB for the data file and the transaction log file.
Jack_Kaye
New Member
New Member

--
18 Mar 2008 12:35 PM
I'm running in Full Recovery but have the backup not only set to do a Full Backup but also have the LOG in the backup as well. It was my understanding that this should trim the log file out when it gets backed up.
Jack_Kaye
New Member
New Member

--
18 Mar 2008 02:25 PM
I run a Full backup once/day. I have not run any rebuild on indexes for over a year.
Jack_Kaye
New Member
New Member

--
20 Mar 2008 07:44 AM
The TLOG is backed up at the same time the database is according to the maintenance plan. So this takes place 1/day in the evening when nobody is in the database.
Jack_Kaye
New Member
New Member

--
20 Mar 2008 12:05 PM
Thanks for all the feedback. My belief is that we can get by with 1 hour loss of data. Therefore, I assume I should setup the TLOG backup to run every hour during the prime business day. Should the FULL backup also backup the TLOG at that time too?

With the TLOG as large as it is presently, if I were to switch to an hourly backup of the TLOG, is there a way to make sure that I don't end up with 8-9 HUGE TLOG files per day?
Jack_Kaye
New Member
New Member

--
20 Mar 2008 12:08 PM
quote:

Originally posted by: Pro Pete
Make sure the backup of the TLOG does not include the NO_TRUNCATE option as that would leave all the backed up log entries still sitting in the Log file and not marked for re-use.

Also check how much of the Log file is actually being used, because truncation of the Log does not release space to the OS, only allows it to be re-used by SQL. You may find that it has grown that big gecause it was used at some poiint and has never been shrunk back.


In the Database Maintenance Plan, under the Transaction Log Backup tab, I do not see an option for NO_TRUNCTATE. Is there another location that can be verified that it is not set?
Jack_Kaye
New Member
New Member

--
21 Mar 2008 09:28 AM
I just created a new maintenance job for the TLOG backup to run every hour. I noticed that the TLOG file is still 15GB after the backup has completed. Am I missing something that should be trimming down the size of the TLOG after it is backed up?
You are not authorized to post a reply.

Acceptable Use Policy