Get db log space usage information

get-db-log-space-used.sql

-- Get db log space usage information
CREATE TABLE #LogSpaceUsage (
    DatabaseName NVARCHAR(100),
    TotalLogSize_MB DECIMAL(18, 2),
    LogSpaceUsed_MB DECIMAL(18, 2),
    LogSpaceUsed_Percent DECIMAL(5, 2),
    LogSpaceFree_MB DECIMAL(18, 2)
)

-- Loop through all databases
DECLARE @DatabaseName NVARCHAR(100)
DECLARE @SQL NVARCHAR(MAX)

DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE' -- Consider only online databases

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Execute query to get log space usage information for each database
    SET @SQL = '
        INSERT INTO #LogSpaceUsage (DatabaseName, TotalLogSize_MB, LogSpaceUsed_MB, LogSpaceFree_MB, LogSpaceUsed_Percent)
        SELECT 
            ''' + @DatabaseName + ''' AS DatabaseName,
            CAST(total_log_size_in_bytes * 1.0 / 1024 / 1024 AS DECIMAL(18, 2)) AS TotalLogSize_MB,
            CAST(used_log_space_in_bytes * 1.0 / 1024 / 1024 AS DECIMAL(18, 2)) AS LogSpaceUsed_MB,
            CAST((total_log_size_in_bytes - used_log_space_in_bytes) * 1.0 / 1024 / 1024 AS DECIMAL(18, 2)) AS LogSpaceFree_MB,
            CAST((used_log_space_in_bytes * 1.0 / total_log_size_in_bytes) * 100 AS DECIMAL(5, 2)) AS LogSpaceUsed_Percent
        FROM ' + QUOTENAME(@DatabaseName) + '.sys.dm_db_log_space_usage'
        
    EXEC sp_executesql @SQL
    
    FETCH NEXT FROM db_cursor INTO @DatabaseName
END

CLOSE db_cursor
DEALLOCATE db_cursor

-- Select results
SELECT * FROM #LogSpaceUsage ORDER BY 4 DESC

-- Drop the temporary table
DROP TABLE #LogSpaceUsage