SQL 2017 new DMF: Managing VLOGs in our databases

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.

Size Increased Number of Vlogs
<=1MB 3
1MB to 64MB 4
64MB to 1GB 8
1GB and up 16

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

vlogs sys.dm_db_log_info groupped

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

vlogs sys.dm_db_log_info cross database

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’

vlogs sys.dm_db_log_info and size

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’

vlogs sys.dm_db_log_info with size and recommendation

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.

How you log in to Simple Talk has changed

We now use Redgate ID (RGID). If you already have an RGID, we’ll try to match it to your account. If not, we’ll create one for you and connect it.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue

Simple Talk now uses Redgate ID

If you already have a Redgate ID (RGID), sign in using your existing RGID credentials. If not, you can create one on the next screen.

This won’t sign you up to anything or add you to any mailing lists. You can see our full privacy policy here.

Continue