November 17, 2004 at 1:28 pm
We have T-log size maximum set to 2 gigs. The t-logs are backed up
hourly to a different server. The backup server went down, and the
T-log file hit 2 gigs and the database stopped accepting writes and
gave an error. Why did this happen?
When T-log backups are run, does it actually reduce the size of the
T-logs themselves?
November 17, 2004 at 11:31 pm
All changes to the database are first written to the trx log before written to the data files (which may occur later). A trx is deemed to be committed only after the changes have been written to the trx log. Thus, your database stopped accepting writes simply because it could no longer perform as designed.
When trx logs are backed-up, the size of the active portion is reduced, but the physical size will remain unless you choose to shrink it (and you might not want to do it because of the reasons listed here).
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
November 18, 2004 at 7:25 am
To add to Peter's comments, even if you shrink the files it doesn't happen immediately.
You are better off allowing the TL to grow. Continuing to back it up and run the shrinkfile command as needed.
-SQLBill
November 22, 2004 at 5:37 pm
Make sure you see if your TLog file is configured to autogrow. Even though this causes MAJOR performance hits if you reach this threshhold, it will atleast not cause a failure. You should allocate a sufficient (be overly cautious) amount of space for your Tlog and set it to autogrow. Routinely monitor this to ensure that you aren't autogrowing very frequently.
If you do not have your TLog set to autogrow and you fill it up, your database will fail.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply