Transaction log space used in percent - how to clear or empty 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 &lt; 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 &quot;zero&quot; (and by &quot;it&quot; I mean percent of transaction log used). I have tried `checkpoint' but that did not help.
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.