SQL SERVER – How to Find Free Log Space in SQL Server?

One of the most popular question on this blog is about how to shrink the log file. I have previously written about that subject on the following blogs. However, today we are not going to talk about Shrinking the log file but rather going to talk about how to monitor free log space in SQL Server.

Before we continue to read this blog post here are some blog post where I have explained what you should do when the log file grows too big.

Now let us talk about how to we can monitor free log space in SQL Server.

Method 1: Using SQLPERF

DBCC SQLPERF ('LOGSPACE')
GO

Here is the script which you can run to get used space across all the databases. The reason, I prefer the second method over this method is because I can write my own logic when I go for method 2.

Method 2: sys.dm_db_log_space_usage

SELECT total_log_size_in_bytes*1.0/1024/1024 total_log_size_in_MB,
used_log_space_in_bytes*1.0/1024/1024 used_log_space_in_MB,
(total_log_size_in_bytes - used_log_space_in_bytes)*1.0/1024/1024
AS free_log_space_in_MB
FROM sys.dm_db_log_space_usage; 

I prefer this method over the method 1 as I can write this for a single database as well as I can insert this data easily into a table.

One of the task which I do frequently is that I monitor all of my log files and make sure that they have enough free space in it.

Reference: Pinal Dave (https://blog.sqlauthority.com)