Below you will find the code to gather information about the virtual log files for each database on the server. Simply select the text and copy to the Clipboard.
–From https://gallery.technet.microsoft.com/scriptcenter/SQL-Script-to-list-VLF-e6315249
— Kev Riley
DECLARE @query VARCHAR(100)
DECLARE @dbname SYSNAME
DECLARE @vlfs INT /*table variable used to ‘loop’ over databases */
DECLARE @databases TABLE (dbname SYSNAME)
INSERT
INTO @databases
/*only choose online databases */
SELECT name
FROM sys.databases
WHERE state = 0 /*table variable to hold results */
DECLARE @vlfcounts TABLE (dbname SYSNAME, vlfcount INT)
/*changes in the output of DBCC loginfo from SQL2012 mean we have to determine the version */
DECLARE @MajorVersion TINYINT
SET @MajorVersion = LEFT(CAST(SERVERPROPERTY(‘ProductVersion’) AS NVARCHAR(MAX))
, CHARINDEX(‘.’, CAST(SERVERPROPERTY(‘ProductVersion’) AS NVARCHAR(MAX))) – 1)
IF @MajorVersion < 11 /* pre-SQL2012 */
BEGIN
DECLARE @dbccloginfo TABLE (fileid SMALLINT,
file_size BIGINT,
start_offset BIGINT,
fseqno INT,
[status] TINYINT,
parity TINYINT,
create_lsn NUMERIC(25, 0))
WHILE EXISTS
(SELECT TOP 1 dbname
FROM @databases)
BEGIN
SET @dbname =
(SELECT TOP 1 dbname
FROM @databases)
SET @query = ‘DBCC LOGINFO (‘ + ”” + @dbname + ”’) ‘
INSERT
INTO @dbccloginfo
EXEC (@query)
SET @vlfs = @@rowcount
INSERT @vlfcounts
VALUES (@dbname,@vlfs)
DELETE FROM @databases
WHERE dbname = @dbname
END /*while */
END
ELSE
BEGIN
DECLARE @dbccloginfo2012 TABLE
(RecoveryUnitId INT,
fileid SMALLINT,
file_size BIGINT,
start_offset BIGINT,
fseqno INT,
[status] TINYINT,
parity TINYINT,
create_lsn NUMERIC(25, 0))
WHILE EXISTS
(SELECT TOP 1 dbname
FROM @databases)
BEGIN
SET @dbname =
(SELECT TOP 1 dbname
FROM @databases)
SET @query = ‘DBCC LOGINFO (‘ + ”” + @dbname + ”’) ‘
INSERT
INTO @dbccloginfo2012
EXEC (@query)
SET @vlfs = @@ROWCOUNT
INSERT @vlfcounts
VALUES (@dbname,@vlfs)
DELETE FROM @databases
WHERE dbname = @dbname
END /*while */
END
/*output the full list */
SELECT ‘Virtual Log File Counts’ AS ‘Status’, dbname, vlfcount
FROM @vlfcounts
ORDER BY vlfcount DESC