I have a database which has the data of size of 50GB. This doesn't
change daily as we delete millions of records and add new records so
the mdf size almost remains constant. Now the log file (ldf) size
changes, mostly during the weekend this change occurs. Last before
saturday ldf size was 0.01GB and then that monday it changed to 2.2GB
which almost remained constant through out the week then again on last
saturday it changed to 0.057GB and it is still on that value. I read
somewhere that this log size should increase or decrease according to
the database size. Is that true?
Then my question is what really changes this value so drastically? I am
don't have much knowledge on SQL architecture but would someone tell me
if this is healthy?

By the way my database is on SQL server 2000 and running on Windows
2003.

Thanks alot

It depends on what Recovery Mode your Database is set too. If the
Recovery Mode is Simple then the Log file will increase when
Transactions are in progress. When the Transactions have been
committed to the Database the Log File will decrease in size to it
original size (or there abouts).

If you're using Full Recovery mode, which I doubt you are, then the Log
File will keep on increasing until you Backup the Log and/or run DBCC
ShrinkFile.

The reason why your Log File increases dramatically at the weekend is
because that is when the bulk of your processing occurs?? The fact
that the Log File has reduced probably means that you are using Simple
Recovery Mode. Or someone else has truncated the Log.

HTH

Barry