Listing Log File Sizes of All Databases in SQL Server

Hello everyone,

In this article, I will provide information about listing log file sizes of all databases in SQL Server.

In SQL Server, in some cases we may want to list the log file sizes of all databases.

You can easily do this with the help of the code below.

WITH fs
AS (SELECT database_id,
           type,
           size * 8.0 / 1024 SIZE
    FROM sys.master_files)
SELECT name,
       (
           SELECT SUM(SIZE)
           FROM fs
           WHERE type = 0
                 AND fs.database_id = db.database_id
       ) DataFileSizeMB,
       (
           SELECT SUM(SIZE)
           FROM fs
           WHERE type = 1
                 AND fs.database_id = db.database_id
       ) LogFileSizeMB
FROM sys.databases db;

When you run the code, you will get a result like the one below.

As you can see, the log file sizes of all databases are listed.

Good luck to everyone in business and life.