0

I am trying to learn more about the transaction log, which I think is the bottleneck of many Stored Procedures (used for reporting) which do data modifications.

So I tried below:

select * from sys.dm_db_log_space_usage;
update dbo.A
set col1 = 'hello'
where id < 500000;
select * from sys.dm_db_log_space_usage;

So now the log file used is 1.8%. How can I clear it? I thought when the transaction is committed it resets to "zero" (and by "it" I mean percent of transaction log used). I have tried `checkpoint' but that did not help.

1
1

The log file is divided into several Virtual Log Files internally. The percent usage is based on this. If you have, say, 4 VLFs of equal size, you will never have less than 25% usage. There is always the head of the log and that VLF is in use.

The percentage can be different even when only one VLF is used, since VLFs in the same ldf file can be of different sizes.

DBCC LOGINFO will tell you which VLFs are in use. Status column. 2 means in use, 0 means not in use.

If you have more than one VLF in use, then the log_reuse_wait_desc column in sys,databases will tell you why SQL Server has more than one in use. Is it waiting for a log backup? Is replication holding it up? Etc.

2
  • Thanks, say I have an SP with two update statements both with implicit transactions. Will the log file grow less if I wrap both statements in one explicit transaction? In theory this should make the SP faster...
    – xhr489
    Feb 8 at 9:58
  • 1
    No, the growth should be pretty much unchanged. Each transaction will generate a begin and a commit log record to the log. But those log records will be neglectable in size compared to the log records that contains the actual data. As for making the sp faster, yes, fewer transactions can do that since each commit causes a sync I/O to the log. But you increase the exposure to blocking, so it can go both ways! Feb 8 at 17:30
2

The guide David Browne linked should explain everything you need, so I highly concur that you should read it. But the five second version is the Transaction Log will grow until a backup is taken of it to ensure the log itself is hardened (aside from the transaction being committed to disk). When the Transaction Log grows, it'll use the reserved free space it has available inside the log file until it runs out of space in the file itself, then the file itself will undergo a growth operation on the disk.

Once a growth operation occurs, the size of the log file on disk will remain that size unless an explicit shrink operation occurs, but internally on SQL Server, the space consumed by the log file is flushed and re-used when a log backup occurs. So while from the OS's perspective, the log file may be one size, the actual available reserved space inside that log file is managed by SQL Server and consumed until it runs out and needs to grow the log file again or a backup occurs to flush the used space so it can be re-used.

One last thing I meant to add, is that your stored procedure performance is almost certainly not related to how full your Transaction Log is. If you post a new question with the execution plan of your stored procedure, we likely can point you in the right direction of the actual root cause.

8
  • 1
    Thanks, yes I am reading it. My database is in simple recovery mode so I don't understand why checkpoint does not work...
    – xhr489
    Feb 7 at 22:49
  • Hi, I moved the accepted answer. Hope it is okay. I can see the log file grows a lot when the SP runs (90% of the space). I think it is strange that used_log_space_in_percent is not the same as what I see in Redgate...
    – xhr489
    Feb 8 at 14:09
  • 1
    @xhr89 No problem with moving your accepted answer :) (Tibor is a very knowledgeable guy so you can rely on his information). It depends on when you're comparing the physical file size of the log file vs the used_log_space_in_percent column. If a backup occurred in between or if a large transaction was rolled back, then even though the size on disk is large, internally the space will be re-used by SQL Server, so the used_log_space_in_percent will be less than the actual file size of the log file. Regardless, it sounds like your SP is creating big transactions. So the performance...
    – J.D.
    Feb 8 at 14:27
  • 1
    ...issues you're experiencing with your SP are most likely due to the queries the SP is running, not related to the growth or size of your log file. The growth of your log file is actually the result of your SP doing heavy work. So again if you want to post a new question with your SP's execution plan, we probably can help you solve your actual performance issues.
    – J.D.
    Feb 8 at 14:29
  • 1
    @xhr489 Gotcha... Maybe whoever installed SQL Server Management Studio for you can be convinced to do the same for SentryOne Plan Explorer 😉. It extends the functionality that SSMS is lacking, and is highly recommended in the SQL community, especially if you're learning how to optimize SQL. It would be pretty strange for you to have access to be messing about with the Transaction Logs but not be allowed to use SentryOne Plan Explorer to view your queries' execution plans. :)
    – J.D.
    Feb 8 at 17:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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