Is there a way to run sys.dm_db_log_space_usage for all databases?

Question

I'm looking to create a monitoring tool to track changes in transaction log usage on a production server we maintain.

Previously, I used DBCC SQLPERF(LOGSPACE);, which provided a list of all databases and their current transaction log memory status. However, Microsoft seem to suggest from 2012, the log details should be viewed from sys.dm_db_log_space_usage, which provides similar detail, but seems to be database specific, rather than giving a view of the server overall (i.e. you must be connected to the database you wish to use).

https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-sqlperf-transact-sql?view=sql-server-ver15

I guess my question is two fold:

1. Is there a way to use sys.dm_db_log_space_usage for all databases?
2. Are there any reason the DBCC SQLPERF(LOGSPACE); should not be used?

Answer 1

Is there any reason the DBCC SQLPERF(LOGSPACE); should not be used?

Starting with SQL Server 2012 (11.x), use the sys.dm_db_log_space_usage DMV instead of DBCC SQLPERF(LOGSPACE), to return space usage information for the transaction log per database

Is there a way to use sys.dm_db_log_space_usage for all databases?

You can use the following cursor. Maybe it required a more development;

CREATE TABLE [dbo].[Tbl_DbSizes](
    [database_id] [int] NULL,
    [total_log_size_in_bytes] [bigint] NULL,
    [used_log_space_in_bytes] [bigint] NULL,
    [used_log_space_in_percent] [real] NULL,
    [log_space_in_bytes_since_last_backup] [bigint] NULL
) ON [PRIMARY]

GO
DECLARE 
    @queryAsList VARCHAR(MAX) ,@DbName AS VARCHAR(100)

DECLARE Db_List CURSOR
FOR
SELECT name  FROM sys.databases 

OPEN Db_List;

FETCH NEXT FROM Db_List INTO 
   @DbName 

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @queryAsList = 'INSERT INTO Tbl_DbSizes select * from '+ @DbName + '.' + 'sys.dm_db_log_space_usage'
        EXEC(@queryAsList)

        FETCH NEXT FROM Db_List INTO 
           @DbName
    END;

CLOSE Db_List;

DEALLOCATE Db_List;
SELECT name as databasename , total_log_size_in_bytes  ,
used_log_space_in_bytes,used_log_space_in_percent,log_space_in_bytes_since_last_backup 
FROM Tbl_DbSizes INNER JOIN sys.databases databaseinfo 
ON databaseinfo.database_id= Tbl_DbSizes.database_id

TRUNCATE TABLE [Tbl_DbSizes]


+--------------------+-------------------------+-------------------------+---------------------------+--------------------------------------+
|    databasename    | total_log_size_in_bytes | used_log_space_in_bytes | used_log_space_in_percent | log_space_in_bytes_since_last_backup |
+--------------------+-------------------------+-------------------------+---------------------------+--------------------------------------+
| master             |                 2088960 |                  729088 |                  34.90196 |                               270336 |
| tempdb             |                 8380416 |                  675840 |                  8.064516 |                               299008 |
| model              |                 8380416 |                 1617920 |                  19.30596 |                                73728 |
| msdb               |                 9428992 |                 1208320 |                  12.81494 |                                86016 |
| DWDiagnostics      |                75489280 |                 6467584 |                  8.567553 |                               253952 |
| DWConfiguration    |                 8380416 |                  626688 |                  7.478006 |                               253952 |
| DWQueue            |                 8380416 |                 1404928 |                  16.76442 |                               253952 |
| DemoDb             |                 8380416 |                 1732608 |                  20.67449 |                               266240 |
| ReportServer       |                75489280 |                13873152 |                  18.37765 |                               274432 |
| ReportServerTempDB |                75489280 |                 1925120 |                   2.55019 |                               245760 |
+--------------------+-------------------------+-------------------------+---------------------------+--------------------------------------+

Answer 2

I could not get it to work on my machine, but I found a lot about sp_MSForEachDB. You could give it a try:

declare @findKeySQL varchar(1000)
SET @findKeySQL = 'IF ''[?]'' NOT IN (''[master]'', ''[model]'',''[msdb]'', ''[tempdb]'')
        USE [?] select * from sys.dm_db_log_space_usage'
EXEC sp_MSForEachDB @findKeySQL

Hope this works for you!

Answer 3

Combining the methods from the previous two comments by Newman and dylenv, I wrote a basic script to throw the results into a temp table so it can be filtered or manipulated as needed.

declare
    -- Table already multiplies percent by 100
    -- Use the whole number percent
    -- (e.g. 25 instead of 0.25)
    @Percent_Used_threshold real = 25

drop table if exists ##log_space;

create table ##log_space (
    database_id int primary key
    , [db_name] sysname not null
    , total_space_gb float not null
    , used_space_gb float not null
    , remaining_space_gb float not null
    , used_log_space_in_percent real not null
);

declare
    @sql varchar(max) = '

declare
    @decimal_places int = 4
;

insert into
    ##log_space
select
    db.database_id
    , db.[name] as [db_name]
    , round(
        cast(log_space.total_log_size_in_bytes as float) / power(1024, 3)
        , @decimal_places
    ) as total_space_gb
    , round(
        cast(log_space.used_log_space_in_bytes as float) / power(1024, 3) 
        , @decimal_places
    ) as used_space_gb
    , round(
        cast(log_space.total_log_size_in_bytes - log_space.used_log_space_in_bytes as float) / power(1024, 3)
        , @decimal_places) as remaining_space_gb
    , round(log_space.used_log_space_in_percent, @decimal_places) as used_log_space_in_percent
from
    ?.sys.dm_db_log_space_usage as log_space
inner join
    ?.sys.databases as db
    on log_space.database_id = db.database_id
;'

exec sys.sp_MSforeachdb @sql

select
    *
from
    ##log_space
where
    used_log_space_in_percent >= @Percent_Used_threshold
order by
    used_log_space_in_percent desc

Answer 4

Best for running queries in many dbs is sp_InEachDb by @AaronBertrand, so first run that script in master (or other db) to create sp_InEachDb and then you can run something like this:

-- Make a temp table to store results from each db
drop table if exists #results
create table #results ( Db nvarchar(128), database_id int, total_log_size_in_bytes bigint, used_log_space_in_bytes bigint, used_log_space_in_percent real, log_space_in_bytes_since_last_backup bigint) 

-- run the query in each db, putting results into our temp table
exec sp_ineachdb  @command = '
insert into #results
select db_name(), * from sys.dm_db_log_space_usage
', @name_pattern = 'tenant_%' -- whatever you like here, if anything

-- let's see what it gave us
select * from #results 
order by Db

Check out these articles for more explanation of sp_ineachdb. Don't bother writing a proc yourself, this will be much better. sp_MSForEachDB is ok in many cases but has quite a few problems, so much better to use sp_ineachdb and save yourself headaches down the road.