USE master;
go

-- Create log table in master (or in your DB admin database):
IF NOT EXISTS ( SELECT  *
                FROM    sys.tables
                WHERE   name = 'Logspace' ) 
    CREATE TABLE dbo.Logspace
        (
          DatabaseName SYSNAME ,
          LogSizeMB FLOAT ,
          LogSpaceUsedPct FLOAT ,
          StatusNum INT ,
          Logdate DATETIME DEFAULT ( GETDATE() )
        );
GO



ALTER PROCEDURE dbo.GetLogSpace @reportOnly BIT = 1, @retentionDays INT = 730
/*
	Source:				Jen McCown, www.MidnightDBA.com/Jen
	Purpose:			Log the current log space free/used in the table dbo.Logspace.
	Example execution:	EXEC dbo.GetLogSpace;
						EXEC dbo.GetLogSpace @reportOnly = 0;				-- Insert the logspace data (default 2 year retention)
						EXEC dbo.GetLogSpace @reportOnly = 1;				-- Just select the current logspace data.
						EXEC dbo.GetLogSpace @reportOnly = 0, @rententionDays = 365;	-- Insert the logspace data (1 year retention)

	Discussion: 
		You could schedule this to run every day/week/month, to track log usage
		over time. Or you could just run the report to see the current usage.
		This SP has a default log retention of 2 years; log data will only be 
		deleted when a new logged run is executed.

		Note that I'm NOT advocating the shrinking of log files willy nilly.
		Your logs need room to breathe, and if they regularly take up 150Gb, 
		they probably need 150Gb. In the cases where you've missed log backups
		on a FULL mode database, you need to back up that log, then (MAYBE) 
		shrink the log file to a more reasonable size.
				*** TL;DR: Don't habitually shrink log files! ***

		Use and/or modify this script at your own discretion.
*/
AS 
    IF @reportOnly = 0 
        BEGIN
            INSERT  INTO dbo.Logspace
                    ( DatabaseName ,
                      LogSizeMB ,
                      LogSpaceUsedPct ,
                      StatusNum 
			            
                    )
                    EXEC sp_executesql N'dbcc sqlperf(logspace)';

            DELETE  dbo.LOGSPACE
            WHERE   Logdate < ( GETDATE() - @retentionDays );
			
			SELECT  DatabaseName ,
                    LogSizeMB ,
                    LogSpaceUsedPct ,
                    LogSizeMb - (LogSizeMb * LogSpaceUsedPct / 100) AS LogSpaceUNUSEDmb ,
					SUM(LogSizeMb - (LogSizeMb * LogSpaceUsedPct / 100)) OVER ( ) AS LogSpaceUNUSEDmb_TOTAL ,
                    StatusNum ,
                    Logdate
            FROM    dbo.LOGSPACE
            WHERE   logdate = ( SELECT  MAX(logdate)
                                FROM    dbo.LOGSPACE
                              )
            ORDER BY LogSpaceUNUSEDmb DESC;
        END  
    ELSE 
        BEGIN
            CREATE TABLE #Logspace
                (
                  DatabaseName SYSNAME ,
                  LogSizeMB FLOAT ,
                  LogSpaceUsedPct FLOAT ,
                  StatusNum INT
                );
	
            INSERT  INTO #Logspace
                    ( DatabaseName ,
                      LogSizeMB ,
                      LogSpaceUsedPct ,
                      StatusNum 
			            
                    )
                    EXEC sp_executesql N'dbcc sqlperf(logspace)';

            SELECT  DatabaseName ,
                    LogSizeMB ,
                    LogSpaceUsedPct ,
                    LogSizeMb - (LogSizeMb * LogSpaceUsedPct / 100) AS LogSpaceUNUSEDmb ,
                    SUM(LogSizeMb - (LogSizeMb * LogSpaceUsedPct / 100)) OVER ( ) AS LogSpaceUNUSEDmb_TOTAL ,
                    StatusNum ,
                    NULL AS Logdate
            FROM    #Logspace
            ORDER BY LogSpaceUNUSEDmb DESC;
            DROP TABLE #Logspace;
        END
GO

EXEC dbo.GetLogSpace @reportOnly = 1;