get-top-unused-logspace.sql

DECLARE @logspace TABLE (
	db_name VARCHAR(255)
	,size FLOAT
	,used FLOAT
	,[status] INT
	)

INSERT INTO @logspace
EXEC ('dbcc sqlperf(logspace)');

--**uncomment line 12,13, and 25 to sum**
--SELECT sum([unused space (GB)])
--FROM (
SELECT db_name
	,sys.master_files.name AS db_name_log
	,logspace.size * (100 - used) / 100 / 1024 AS [unused space (GB)]
	,recovery_model_desc
FROM @logspace AS logspace
	,sys.databases
	,sys.master_files
WHERE logspace.db_name = sys.databases.name
	AND sys.databases.database_id = sys.master_files.database_id
	AND sys.master_files.type = 1
ORDER BY [unused space (GB)] DESC
	--  ) AS T