August 13, 2003 at 5:17 pm
Why does the Transaction Log keep growing if even if you apply transaction log backups constantly? Isn't SQL Server reusing the inactive portion of the log after each transaction log backup?
I understand that after truncating the transaction log, the physical log file remains the same. It is not until you shrink the transaction log that the physical log reduces in physical size.
The transaction log consists of an active and inactive portion. The active portion is used to restore the database since last backup. The inactive portion is just a waste of space. When you backup the transaction log, SQL Server truncates the inactive portion so that it can be reused.
Then how come the transaction log keeps physically growing in size even though full backups and transaction log backups are being applied daily? Because the transaction log is being backed up, isn't the inactive portion being truncated and reused which means that the transaction log physical size should remain the same?
Thanks in advance,
Billy
August 14, 2003 at 4:45 pm
The transaction log backup should truncate the log file to the point of the last backed-up entry. A full backup will not truncate the log, only a log backup will truncate the log. You may check to make sure there are no large open transactions in the database (dbcc opentran), if you're using replication, those transactions are stored until they can be replicated. If you run a "DBCC SQLPERF(LOGSPACE)" command, does it show that the logfile for that DB is almost full (ie-90%+)?
-Dan
-Dan
August 14, 2003 at 5:37 pm
Dan:
Thanks for the tip. Using dbcc opentran, there was one stray open transaction. It makes sense now!
Billy
August 16, 2003 at 7:32 am
Another reason this can happen is if you're running transactional replication and the log reader isn't running.
Andy
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply