I'm wondering why my transaction log would only be 2 MB on my 40 GB database when I have recovery mode set to full and unlimited file growth set on the transaction log. Any ideas?
Edit: I'd say there is probably a few hundred MB inserted every day and a lot of updates going on. It's a database that drives a fairly active website.
if you are doing regular full backups, those will truncate the transaction log.
Because you back up the log, and that's what it's supposed to do?
Sidenote (given that I can't comment): A full backup does not truncate the log. Only log backups, or TRUNCATE_ONLY commands, truncate the log.
Of the 40 GB how many data is changed every day? Transaction log only traces logged operations (insert, delete, update) and never traces read operation or bulk copied insertt using BCP or other bulk command (actually I do not remember if the T-SQL command to bulk load data is availabe on SQL2000 or not).
If your feel your logged operation should take more than 2 Mb each day examine scheduled jobs to see if someone is periodically dumping the log