I have a strange situation with my SQL Server 2000 setup that I can’t figure out. My database is about 40 GB in size, but the transaction log file is only showing 2 MB which seems way too small.
I have the recovery model configured to full recovery mode and the transaction log has unlimited growth enabled. With these settings, I would expect the log file to be much larger.
This database supports a busy website with lots of activity. We probably add several hundred megabytes of new data daily, plus there are tons of update operations happening throughout the day.
Given all this database activity and my current configuration, why would the transaction log stay so small? Is this normal behavior or am I missing something in my setup?
i agree with the log backup theory. if your backups are truncating the log, it will stay small, even in full recovery model. also double check your settings to make sure simple recovery isn’t enabled by mistake. it’s a tricky issue for sure!
hmm that’s really interesting - are you absolutely certain about those activity levels? because honestly 2MB seems almost suspiciously small for that kind of workload. what happens if you manually run a checkpoint and then check the log size again? also curious - when was the last time you actually monitored the log file size during peak activity hours?
This behavior typically indicates that transaction log backups are running automatically in the background. Even with full recovery mode, regular log backups will truncate the inactive portion of the transaction log, keeping it small while preserving recoverability.
I encountered this exact scenario on a production system where the DBA had configured automated log backups every 15 minutes through maintenance plans. The log would grow during activity bursts but get truncated shortly after each backup completed.
Check your SQL Server Agent jobs and any third-party backup software that might be performing log backups without your knowledge. You can also query sys.backup_history or examine the SQL Server error log for backup entries. If log backups are indeed running, this explains the small file size despite heavy database activity.