SQL Server 2017 brings to us some new interesting DMV’s and DMF’s, one of them is sys.dm_db_log_info. This new DMF allows us to manage VLOGs in our databases.

The log files are divided in smaller slices called virtual log files, or vlog. Operations such as log truncations happens over an entire vlog. For example, when a log trunction happens, only the vlogs without any active transaction can be truncated, so if a vlog has an active transaction, the entire vlog can’t be truncated.

The number of vlogs can affect the performance of the transaction log. Each time the size of the transaction log increases, such as in an auto-growth event, for example, new vlogs are created inside the transaction log. If there are too many auto-growth happening, the number of vlogs will increase too much and this will affect the log performance.

There is a relation between the log space and the number of vlogs. When the log increases, the number of vlogs created depends on the space increased, according to this table.

It’s a good idea to monitor the number of vlogs in each database, however until now the only way to do this was with the statement DBCC LOGINFO, which has a result difficult to automate.

Now, in SQL Server 2017, we can use the new DMF sys.dm_db_log_info to manage the number of vlogs in our databases.

This function receives the name of the database as parameter and returns a record for each vlog in each log file of the database. In order to get the number of vlogs, we just need to aggregate the records.

select file_id,count(*) TotalVlogs
from sys.dm_db_log_info(db_id(‘Sales’))
group by file_id

We can build a cross apply between a query over sys.databases and the execution of this function to retrieve the information for each database.

select name,file_id, TotalVlogs
from sys.databases sdb
cross apply
    (select file_id,count(*) TotalVlogs from
          sys.dm_db_log_info(db_id(sdb.name))
          group by file_id) log

We also need to include the size of the log to give meaning to the information. The size of each log file is in the sys.master_files DMV. Joining the results by database name and file_id, the query will be this:

with qry as
   (select name,file_id, TotalVlogs
           from sys.databases sdb
        cross apply
           (select file_id,count(*) TotalVlogs
                from sys.dm_db_log_info(db_id(sdb.name))
                group by file_id) log
    )
select qry.name,qry.file_id,TotalVlogs,(size * 8)/1024 [Size]
from qry
     inner join sys.master_files smf
     on db_name(smf.database_id)=qry.name
        and smf.file_id = qry.file_id
where smf.type_desc=‘LOG’

Finally, for a better understand of the result, we can include the recommended number of vlogs as the result, according to the size table above. A simple case statement will do it.

with qry as
      (select name,file_id, TotalVlogs
              from sys.databases sdb
          cross apply
              (select file_id,count(*) TotalVlogs
                  from sys.dm_db_log_info(db_id(sdb.name))
                  group by file_id) log
      )
select qry.name,qry.file_id,
       TotalVlogs,(size * 8)/1024 [Size],
       case
           when (size * 8)/1024 <1 then 3
           when (size * 8)/1024 < 64 then 4
           when (size * 8)/1024 < 100 then 8
           else 16
       end RecommendedVLogs
from qry
     inner join sys.master_files smf
     on db_name(smf.database_id)=qry.name
        and smf.file_id = qry.file_id
where smf.type_desc=‘LOG’

From this point, there are several ways to automate this using SQL Server Agent, for example, sending e-mail alerts when the number of vlogs is way above the recommended values.