please explain transaction logs

[edwinhunt]

hello all,

can someone please explain to me how sql 2000 transaction log works? our recovery model is set to full so i know why my log size is big. however what i'm trying to figure out really is how it behaves. i've been monitoring the actual physical size of the file for the last 3 weeks and i just couldn't figure out any pattern. we do nightly full backup and truncation and basically i would note the size of the file before the backup happens and after. the thing that i find most hard to understand is sometimes on a regular work day the log size will stay the same all thru out the day as if nothing is going on and then sometimes on a different day it just grows to a few gig more. then on a weekend when you would think nothing much is going on the size would grow too to a few gig. after the scheduled backup and truncation sometimes the file will shrink afterwards and then sometimes the file just stays the same. can someone explain why it's like this? is this normal? i'm not having any problem on my server and no error messages on my event log. i'm just curious about this transaction log so any help is very much appreciated.

thanks,
edwin

 

[mrdenny]

Edwin,
When in Full recovery mode the Transaction log keeps a full record of all data changes to the database. If the users are only reading data from the database then there will be no additional entries into the log. If however there are changes (inserts/updates/deleted) in the database, then the log will grow.

I'm assuming that you are looking at the actual size of the file. This can be kind of miss-leading. SQL Server keeps empty space in the database and transaction log file. By default when the log file fills sql will expand the file by 10%. For example if the file is 10 GB and it fills up, SQL will extend the file to 11 GB.

Does this answer your question?

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[Catadmin]

Additionally, there are a few things you can check to see about the occasionally random shrinking. Rightclick the DB and go to Options. Is Auto Shrink checked? If so, then every so often, SQL Server or SQL Server Agent (not sure which one) sends a job that can, not necessarily will, shrink your transaction log's un-used space.

Also check your backup job for the Log. Does it optimize the Database? When you set this option up via the Database Maintanence Planner, it can re-organize your data & index pages and shrink your database sizes by a certain amount when it runs.

Do you have any Shrink Log jobs scheduled to run? This might also cause it. Might also want to verify there is no other DBA running shrink log jobs when you aren't looking.

Usually, Transaction logs get truncated when a Log backup is run or a complete database backup is run. As mrdenny said, the more jobs (updates/inserts/deletes) you run in a short period of time, the bigger the log can grow.

Check out faq962-5742 for more details about backups and logs.



Catadmin - MCDBA, MCSA
Beware the error of pre-emptive poultry inventory!

 

[kishy]

One more reason for your increase in your transaction log may be if you are Maintainence plans to reindex your tables. Reindexing would considerably increase your transaction log size.