I have SQL Server 2005 Database where the transaction log has not updated in a week. The transaction log has recently been truncated due to a situation that used up all available disk space. I have a test database on the same server which is growing daily as expected. What might be the cause of this?
A:
I doubt it's really "not updating" unless you haven't changed the data in the database. Note that the modification date on an MDF/ LDF doesn't typically change, but that doesn't mean the data in the file is not changing. It could be that your DB is in "simple" recovery mode, so the log is working but doesn't grow.
onupdatecascade
2009-08-03 15:26:17
We are running full backups and I know that there are constant data changes. I am attempting to track log growth due to the recent issue we had with large quick growth of the transaction log so I can avoid future issues. Is there a better way to track log growth?
Dan H.
2009-08-03 16:17:18
Can you check my understanding? Is the reason that I'm not seeing the file size grow due to the % of free space in the log? for example if my log is 500 MB with 50% of the log space used, I should not expect to see the log file grow unless the % of log space used reached 100%.
Dan H.
2009-08-03 18:14:00
Check the recovery model of the DB - if it's "Simple," then the log works like this: for each transaction, some of the log is used while the transaction is in play; once the transaction is committed, then that portion of the log is freed, and can be overwritten with new log data later. So if you have a 500mb log, and reasonable transactions, it'll never fill up and so will never grow. The only thing that would make it grow is a very long running transaction, where the "old" log records are never discarded.
onupdatecascade
2009-08-03 20:32:03
If you have recovery model "Full" or "Bulk Logged" then the log *will* keep growing, and the old log records are not freed unless/until you perform a Transaction Log Backup, which copies the freed log records out to a backup file and then frees the space. Full recovery always requires scheduled transaction log backups.
onupdatecascade
2009-08-03 20:34:11