Transaction Log tuning

Question

Transaction Log tuning

Frank Rueff

SSC Enthusiast

Points: 185
More actions
February 16, 2005 at 11:35 am

#85097

We have a vendor telling us that the reason that the transaction log is not truncating is something that we did. It is SQL 2000 SP3a. The log has grown from 10 MBs on Feb 10 to 135MBs today. That was before I ran a script that shrunk it down from 2.2 GBs.
The database is set to full and does a nightly full backup and is supposed to truncate the log on backup. This server is a dual 3.60 GHZ with 4GBs ram and it only host their database of 453MBs. They do run a seperate index file that is 603 MBs.
Is there something that I can do that will prevent this or is it a vendor problem?
Thanks
Frank

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

hoo-t SSChampion Points: 12713 More actions · Answer 1

Frank,

A full backup will not and should not ever truncate a transaction log. If you have your database set to the Full Recovery Model, you should be taking regular transaction log backups, which do truncate the log. You indicate that the transaction log has grown 125 MB in a week's time, and that the database is approximately 1GB (data and index). You should probably, at a minimum, take a transaction log backup once per day. However, if it is a transactional database, you may want to consider transaction log backups more often than that. Once per hour is not unreasonable. Obviously the more often you take the backups, the smaller (in general) the backups will be, and the smaller the transaction log can be. In my shop, we take transaction log backups every 15 minutes in most cases.

Steve

Michael Nutt Newbie Points: 5 More actions · Answer 2

Transaction logs are not truncated when a full backup runs. It sounds like the database was set to Full logging mode at some point which causes the transaction log to grow continually (even though full backups are run) until a transaction log backup is run.

Transaction log backups truncate the log file to free up the space but do not shrink the file. It's common to run a transaction log backup every hour for that size of a database. However, a transaction log backup will fail if the logging mode for the database is set to Simple which logs less and truncates the log on its own schedule (very often).

Frank Rueff SSC Enthusiast Points: 185 More actions · Answer 3

Thank You for the information. I will up the transaction log backups and keep an eye on it. I have them set for every six hours right now. I take it that the way a trans log grows is not dependant on the application that uses it, but on how often I back it up.

Thanks

Frank

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 4

Hi Frank,

At the end of the "Truncating the Transaction Log" article of BOL it is explicitly stated: "Truncation does not reduce the size of a physical log file, it reduces the size of the logical log file. For information on shrinking the size of a physical log file, see Shrinking the Transaction Log."

Also, BOL says that one of the events that cause log truncation is the completion of BACKUP LOG statement. So, the transacion log backup itself does not reduce the size of the file, it only reclaims a free space within it.

At some time (once a week maybe) you should schedule a DBCC SHRINKDATABASE or DBCC SHRINKFILE statement, to reduce the size of the physycal log file. Of course, you cannot reduce it below its original size (the size with which it was created).

Regards,

Goce.

Jane Matheson-154829 SSCertifiable Points: 5522 More actions · Answer 5

To get a quick look (especially when new to this) at the transaction log size and space used : start Enterprise Manager, open your server, select the database of interest (by highlighting it) then go to View\taskpad. You should see a summary of some information about your database including files and how much space is used in each. The bar used to graphically show this is misleading - it doesn't use all the space at the beginning of the file. As hte others usggest, read the BOL articles on shrinking files and transaction logs to get a better understanding.

Frank Rueff SSC Enthusiast Points: 185 More actions · Answer 6

I think it is looking better now. The backups run every 30 minutes for the transaction log and they have been reduced from 100+ MBs to about .5 MBs now. I will shrink the log next week just to make sure that everything is good.

Thanks again to everyone.

Frank

hoo-t SSChampion Points: 12713 More actions · Answer 7

I disagree with the advice given to shrink the log once per week. When you shrink it on a regular basis, you are forcing it to expand when it runs out of space, which obviously will happen when someone, or the application, or maintenance procedure is performing inserts, deletes, or updates. This will cause performance problems for your application and is completely unnecessary. In your case, I would definitely shrink it once to get the size under control. But once it "settles in" at the size that it needs, considering your backup schedule, it should no longer grow unless an unusual occurance happens that causes it to require more space (such as a reindex). You can judge the approximate size the log needs to be by looking at the size of the largest transaction log backup, and adding a little, assuming of course that the largest isn't that big because of unusual circumstances.

In answer to your question about transaction log growth, it IS dependent on the application. Every transaction (with certain exceptions) is written to the transaction log, and then committed to the database. So, log growth is dependent on how your database is updated, and controlled by your backup plan.

Steve