large_vlfs.sql

/*
In SQL 2016, there is a feature to allow effective Backup Compression for databases protected by TDE.
There is however a caution that this feature should not be used when the database has VLFs which are
larger than 4GB. This short script can help identify if any of your databases would be affected.
*/

DECLARE @dbname SYSNAME = 'foo';
--TODO: Add switch to only show noncompliance
--TODO: Add switch to only evaluate databases with TDE active

--------------------------------------------------------------------

DECLARE @size_limit BIGINT = POWER(CONVERT(BIGINT, 2),32); --4GB
DECLARE @vlfs TABLE (
	  [RecoveryUnitId] INT
	, [FileId] INT
	, [FileSize] BIGINT
	, [StartOffset] BIGINT
	, [FSeqNo] INT
	, [Status] INT
	, [Parity] TINYINT
	, [CreateLSN] NUMERIC(25,0)
);

INSERT @vlfs
EXEC('DBCC LOGINFO(''' + @dbname  + ''')');

SELECT
	  @dbname
	, SUM(1) AS [vlf_count]
	, SUM (CASE
		WHEN [FileSize] > @size_limit THEN 1
		ELSE 0
	  END) AS [vlfs_too_large]
FROM @vlfs;