4

After monitoring the Transaction log files for a while I noticed that some of them sometimes behave in a way I did not expect.

Here are two samples for comparison. All data is based on EXEC('DBCC SQLPERF(LOGSPACE);')

Sample 1: This shows the development of a TL file over time. Orange is the physical file size of the log file. Blue is the ammount of data that is used. The total file size does not increase in the moment where about 26GB of it are used, because the file is empty and there is enough volume to place 26 GB of data inside of it without growing the file. enter image description here

Sample 2: This other sample shows that the total file size of this TL file has increased at the moment space were required. About 10 Gb of data has been filled into this file, that has had 25 GB total file size. At the same time the file grew 10 GB. enter image description here

I would have expected that the empty/unused file capacity of the transaction log files always are reused as long as the required ammount of data fits into this space.

So the question is: Why is the empty space allocated by the transaction log file not re-used in sample 2 and instead the file grew again?

Improve this question
1
  • 1
    When a transaction runs it uses space in transaction log to write about transaction and it also reserves space in transaction log file if transaction has to rollback. In your case it would have to increase its space to accomodate space for rollback so it grew.
    – Shanky
    Feb 4, 2016 at 12:18

1 Answer 1

Reset to default
1

You may have got the answer already, but it interested me so I thought I'd take a look, and expand a bit more with sources.

As hinted briefly above, the extra space reserved for a possible rollback. As I can't say it any better, Microsoft TechNet describes it thus:

Each transaction reserves space on the transaction log to make sure that enough log space exists to support a rollback that is caused by either an explicit rollback statement or if an error is encountered. The amount of space reserved depends on the operations performed in the transaction, but generally it is equal to the amount of space used to log each operation. This reserved space is freed when the transaction is completed. - Log Architecture and Management

So basically your transaction required that "extra" space reserved until it was actually committed, as that space was not available in your log file, it grew. Your transaction was committed (and therefore not rolled-back), and so that space was not actually needed.

I won't labour the point further, but just this might be of use too:

The ever-wise Aaron Bertrand talks about some events that SQL Server tracks, including log growth. These might help you track down what's happening with your log autogrows in the future

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.