/*
<documentation>
  <summary>Detailed information about VLF files.</summary>
  <returns>2 data sets: first with agregated vlf information and second with detailed info.</returns>
  <issues>No</issues>
  <author>Kev Riley</author>
  <created>2016-12-07</created>
  <modified>2019-06-25 by Konstantin Taranov</modified>
  <version>1.1</version>
  <sourceLink>https://github.com/ktaranov/sqlserver-kit/blob/master/Scripts/VLF_Info.sql</sourceLink>
  <originalLink>https://gallery.technet.microsoft.com/scriptcenter/SQL-Script-to-list-VLF-e6315249</originalLink>
</documentation>
*/

SET NOCOUNT ON;

/* variables to hold each 'iteration' */
DECLARE @query  nvarchar(200);
DECLARE @dbName sysname;

/* table variable used to 'loop' over databases */
DECLARE @databases TABLE (dbname sysname);
/* only choose online databases */
INSERT INTO @databases
SELECT [name] FROM sys.databases WHERE [state] = 0;

DECLARE @majorVersion tinyint;
SET @majorVersion = LEFT(CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max)),
                         CHARINDEX('.',CAST(SERVERPROPERTY('ProductVersion') AS nvarchar(max))) - 1);

DECLARE DatabaseIteration CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR
SELECT [name] AS DatabaseName FROM sys.databases WHERE [state] = 0;

BEGIN

    OPEN DatabaseIteration;

    DECLARE @DbccLogInfo TABLE(
        RecoveryUnitId int               NULL,
        fileid         smallint      NOT NULL,
        file_size      bigint        NOT NULL,
        start_offset   bigint        NOT NULL,
        fseqno         int           NOT NULL,
        [status]       tinyint       NOT NULL,
        parity         tinyint       NOT NULL,
        create_lsn     numeric(25,0) NOT NULL,
        DBName         sysname           NULL,
        [DBID]         int               NULL
    );

    FETCH NEXT FROM DatabaseIteration INTO @dbName;

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SET @query = N'DBCC LOGINFO (N' + N'''' + @dbName + N''') WITH NO_INFOMSGS;';
        PRINT(@query);

        IF @majorVersion < 11 /* pre SQL 2012 versions */
        BEGIN
            INSERT INTO @DbccLogInfo(
                 fileid
               , file_size
               , start_offset
               , fseqno
               , [status]
               , parity
               , create_lsn
            )
            EXEC sp_executesql @query;
        END;
        ELSE
        BEGIN
            INSERT INTO @DbccLogInfo(
                 RecoveryUnitId
               , fileid
               , file_size
               , start_offset
               , fseqno
               , [status]
               , parity
               , create_lsn
            )
            EXEC sp_executesql @query;
        END;

        UPDATE @DbccLogInfo
        SET    DBName  = @dbName
             , [DBID]  = DB_ID(@dbName)
        WHERE DBName IS NULL;

       FETCH NEXT FROM DatabaseIteration INTO @dbName;

    END;

    CLOSE DatabaseIteration;
    DEALLOCATE DatabaseIteration;

END;

/* output the full list */
WITH CTE_VLF AS (
    SELECT dc.[DBID]
         , dc.DBName
         , MIN(dc.file_size) / 1024 / 1024 AS FileSizeMbMIN
         , MAX(dc.file_size) / 1024 / 1024 AS FileSizeMbMAX
         , AVG(dc.file_size) / 1024 / 1024 AS FileSizeMbAVG
         , SUM(dc.file_size) / 1024 / 1024 AS FileSizeMbSUM
         , COUNT(dc.fileid)                AS VLFCount
     FROM  @DbccLogInfo AS dc
     GROUP BY dc.[DBID]
            , dc.DBName
)
SELECT   CTE_VLF.*
       , CASE WHEN VLFCount      >= 1000 THEN 'Check your VLF count, maybe you need reduce it'
              WHEN FileSizeMbAVG >= 500  THEN 'Check your VLF size, maybe you need decrease it'
              ELSE 'Maybe it is ok'
         END Warnings
FROM     CTE_VLF
ORDER BY VLFCount DESC;

SELECT * FROM @DbccLogInfo;