The Log Space Used (Percent) metric calculates the added size all log files used as a percent of summed allocated size of all data files.
A transaction log continues to grow until its inactive portion is truncated. Transaction log truncation occurs because either:
- the BACKUP LOG statement successfully concluded, or
- if the database is using the Simple Recovery Model, then a CHECKPOINT T-SQL statement executes or SQL Server performed an internal automatic CHECKPOINT. SQL Server performs this action if the log reaches 70% full or when SQL Server determines the length of time to recover from the log, in the event of a failure, exceeds the Recovery Interval option.
Only the inactive portion of the log is ever removed. The inactive portion of the log is the portion of the log up to but not including the start date/time of the oldest open transaction. No matter which method is used to truncate the log, if there is a perpetually open transaction then the log continues to grow in size until it consumes all available disk space.
Reduce the percentage of the log used
If your transaction log size continues to grow despite issuing BACKUP LOG or CHECKPOINT statements, check for a rogue transaction. Begin your research by sorting the Last Batch column on the SQL Processes grid into ascending mode such that the processes containing the oldest batches appear first. Scroll down the list until you find the first user process with a non-zero number of transactions in its Trans column, and then kill this process. Once this is done, perform a CHECKPOINT or BACKUP LOG to reduce the Log File Used Size.