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.

SQL SERVER - How to Find Free Log Space in SQL Server? logspace-800x222

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.

Solarwinds

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

Method 1: Using SQLPERF

DBCC SQLPERF ('LOGSPACE')
GO

SQL SERVER - How to Find Free Log Space in SQL Server? logspace1

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; 

SQL SERVER - How to Find Free Log Space in SQL Server? logspace2

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)

Solarwinds
, , , ,
Previous Post
SQL SERVER – Restore Database Wizard in SSMS is Very Slow to Open
Next Post
SQL SERVER – Simple Script to Create a Login and User for a Specific Database with System Admin Rights

Related Posts

2 Comments. Leave new

  • Tom Wickerath
    May 10, 2018 5:42 am

    “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.”

    I am not seeing any links to other blog posts on this topic.

    Reply

Leave a Reply

Menu