User Rating: 0 / 5

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
 
/*
Tested on SQL Server 2014.  The RecoveryUnitID column is new (I believe) in 2012.  Use
the new DM views if on a later version. 

DBCC LOGINFO fields.......
RecoveryUnitId - No idea what this is for
FileID         – The FileID number as found in sysfiles
FileSize       – The size of the VLF in bytes
StartOffset    – The start of the VLF in bytes, from the front of the
                 transaction log
FSeqNo         – Indicates the order in which transactions have been written to
                 the different VLF files. The VLF with the highest number is
                 the VLF to which log records are currently being written.
Status         – Identifies whether or not a VLF contains part of the active
                 log. A value of 2 indicates an active VLF that can't be
                 overwritten.
Parity         – The Parity Value, which can be 0, 64 or 128
CreateLSN      – Identifies the LSN when the VLF was created. A value of zero
                 indicates that the VLF was created when the database was
                 created. If two VLFs have the same number then they were
                 created at the same time, via an auto-grow event.
chunks less than 64MB = 4 VLFs
chunks of 64MB and less than 1GB = 8 VLFs
chunks of 1GB and larger = 16 VLFs
*/
BEGIN
   SET NOCOUNT ON;
   SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
   SET DEADLOCK_PRIORITY LOW;
   --
   DECLARE @Database SYSNAME;
   DECLARE @LogInfo TABLE ([RecoveryUnitId]    INT ,
                           [FileId]            INT ,
                           [FileSize]          BIGINT ,
                           [StartOffset]       BIGINT ,
                           [FSeqNo]            INT ,
                           [Status]            INT ,
                           [Parity]            TINYINT ,
                           [CreateLSN]         NUMERIC(25,0))
   DECLARE @DBLogInfo TABLE ([Database]          SYSNAME,
                             [RecoveryUnitId]    INT ,
                             [FileId]            INT ,
                             [FileSize]          BIGINT ,
                             [StartOffset]       BIGINT ,
                             [FSeqNo]            INT ,
                             [Status]            INT ,
                             [Parity]            TINYINT ,
                             [CreateLSN]         NUMERIC(25,0))
   --
   DECLARE cDatabase CURSOR FOR
      SELECT sd.name     AS [Database]  
      FROM sys.databases sd
      WHERE sd.state = 0
        AND sd.user_access = 0
      ORDER BY sd.database_id FOR READ ONLY;
   --
   OPEN cDatabase
   FETCH NEXT FROM cDatabase INTO @Database
   WHILE (@@FETCH_STATUS = 0)
      BEGIN
         INSERT INTO @LogInfo
         EXEC('DBCC LOGINFO([' + @Database + '])')
         INSERT INTO  @DBLogInfo
             SELECT @Database,
                    *
             FROM @LogInfo;
         DELETE FROM @LogInfo;
         FETCH NEXT FROM cDatabase INTO @Database
      END -- cDatabase cursor loop
   --
   CLOSE cDatabase;
   DEALLOCATE cDatabase;
   --
   -- Remove comments for more detail.  Poor man's drill down.
   --SELECT *,
   --       FileSize/1024 AS [Size(KB)]
   --FROM @DBLogInfo
   SELECT [Database]         AS [Database],
          COUNT(*)           AS [Total VLFs],
          MIN(FileSize)/1024 AS [Smallest VLF (KB)],
          MAX(FileSize)/1024 AS [Largest VLF (KB)],
          SUM(FileSize)/1024 AS [Total Log Size (KB)]
   FROM @DBLogInfo
   GROUP BY [Database]
   ORDER BY [Database]
END;
 
0
0
0
s2sdefault