{"id":71388,"date":"2017-06-20T06:17:03","date_gmt":"2017-06-20T06:17:03","guid":{"rendered":"https:\/\/www.simple-talk.com\/?p=71388"},"modified":"2021-08-24T13:39:24","modified_gmt":"2021-08-24T13:39:24","slug":"sql2017-managing-vlogs-databases-sys-dm_db_log_info","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/sql2017-managing-vlogs-databases-sys-dm_db_log_info\/","title":{"rendered":"SQL 2017 new DMF: Managing VLOGs in our databases"},"content":{"rendered":"

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

The log files are divided in smaller slices called virtual log files, or vlog<\/strong>. Operations such as log truncations happens over an entire vlog<\/strong>. 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.<\/p>\n

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\u00a0auto-growth happening, the number of vlogs will increase too much and this will affect the log performance.<\/p>\n

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.<\/p>\n\n\n\n\n\n\n\n
Size Increased<\/td>\nNumber of Vlogs<\/td>\n<\/tr>\n
<=1MB<\/td>\n3<\/td>\n<\/tr>\n
1MB to 64MB<\/td>\n4<\/td>\n<\/tr>\n
64MB to 1GB<\/td>\n8<\/td>\n<\/tr>\n
1GB and up<\/td>\n16<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

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.<\/p>\n

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.<\/p>\n

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.<\/p>\n

\n
select<\/span>\u00a0file_id<\/span>,<\/span>count<\/i><\/span>(<\/span>*<\/span>)<\/span>\u00a0TotalVlogs<\/span>
\n from<\/span>\u00a0sys<\/span>.<\/span>dm_db_log_info<\/b><\/span>(<\/span>db_id<\/i><\/span>(<\/span>‘Sales’<\/span>)<\/span>)<\/span>
\n group<\/span>\u00a0by<\/span>\u00a0file_id<\/span> <\/span><\/div>\n

\"vlogs<\/p>\n<\/blockquote>\n

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.<\/p>\n

\n
select<\/span>\u00a0name<\/span>,<\/span>file_id<\/span>,<\/span>\u00a0TotalVlogs<\/span>
\n from<\/span>\u00a0sys<\/span>.<\/span>databases<\/span>\u00a0sdb<\/span>
\n cross<\/span>\u00a0apply<\/span>
\n \u00a0\u00a0\u00a0\u00a0(<\/span>select<\/span>\u00a0file_id<\/span>,<\/span>count<\/i><\/span>(<\/span>*<\/span>)<\/span>\u00a0TotalVlogs<\/span>\u00a0from<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0sys<\/span>.<\/span>dm_db_log_info<\/b><\/span>(<\/span>db_id<\/i><\/span>(<\/span>sdb<\/span>.<\/span>name<\/span>)<\/span>)<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0group<\/span>\u00a0by<\/span>\u00a0file_id<\/span>)<\/span>\u00a0log<\/span> <\/span><\/div>\n

\"vlogs<\/p>\n<\/blockquote>\n

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\u00a0the results by database name and file_id, the query will be this:<\/p>\n

\n
with<\/span>\u00a0qry<\/span>\u00a0as<\/span>
\n \u00a0\u00a0\u00a0(<\/span>select<\/span>\u00a0name<\/span>,<\/span>file_id<\/span>,<\/span>\u00a0TotalVlogs<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0from<\/span>\u00a0sys<\/span>.<\/span>databases<\/span>\u00a0sdb<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0cross<\/span>\u00a0apply<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>select<\/span>\u00a0file_id<\/span>,<\/span>count<\/i><\/span>(<\/span>*<\/span>)<\/span>\u00a0TotalVlogs<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0from<\/span>\u00a0sys<\/span>.<\/span>dm_db_log_info<\/b><\/span>(<\/span>db_id<\/i><\/span>(<\/span>sdb<\/span>.<\/span>name<\/span>)<\/span>)<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0group<\/span>\u00a0by<\/span>\u00a0file_id<\/span>)<\/span>\u00a0log<\/span>
\n \u00a0\u00a0\u00a0\u00a0)<\/span>
\n select<\/span>\u00a0qry<\/span>.<\/span>name<\/span>,<\/span>qry<\/span>.<\/span>file_id<\/span>,<\/span>TotalVlogs<\/span>,<\/span>(<\/span>size<\/span>\u00a0*<\/span>\u00a08<\/span>)<\/span>\/<\/span>1024<\/span>\u00a0[Size]<\/span>
\n from<\/span>\u00a0qry<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0inner<\/span>\u00a0join<\/span>\u00a0sys<\/span>.<\/span>master_files<\/span>\u00a0smf<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0on<\/span>\u00a0db_name<\/i><\/span>(<\/span>smf<\/span>.<\/span>database_id<\/span>)<\/span>=<\/span>qry<\/span>.<\/span>name<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0and<\/span>\u00a0smf<\/span>.<\/span>file_id<\/span>\u00a0=<\/span>\u00a0qry<\/span>.<\/span>file_id<\/span>
\n where<\/span>\u00a0smf<\/span>.<\/span>type_desc<\/span>=<\/span>‘LOG’<\/span> <\/span><\/div>\n

\"vlogs<\/p>\n<\/blockquote>\n

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.<\/p>\n

\n
with<\/span>\u00a0qry<\/span>\u00a0as<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>select<\/span>\u00a0name<\/span>,<\/span>file_id<\/span>,<\/span>\u00a0TotalVlogs<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0from<\/span>\u00a0sys<\/span>.<\/span>databases<\/span>\u00a0sdb<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0cross<\/span>\u00a0apply<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0(<\/span>select<\/span>\u00a0file_id<\/span>,<\/span>count<\/i><\/span>(<\/span>*<\/span>)<\/span>\u00a0TotalVlogs<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0from<\/span>\u00a0sys<\/span>.<\/span>dm_db_log_info<\/b><\/span>(<\/span>db_id<\/i><\/span>(<\/span>sdb<\/span>.<\/span>name<\/span>)<\/span>)<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0group<\/span>\u00a0by<\/span>\u00a0file_id<\/span>)<\/span>\u00a0log<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0)<\/span>
\n select<\/span>\u00a0qry<\/span>.<\/span>name<\/span>,<\/span>qry<\/span>.<\/span>file_id<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0TotalVlogs<\/span>,<\/span>(<\/span>size<\/span>\u00a0*<\/span>\u00a08<\/span>)<\/span>\/<\/span>1024<\/span>\u00a0[Size]<\/span>,<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0case<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0when<\/span>\u00a0(<\/span>size<\/span>\u00a0*<\/span>\u00a08<\/span>)<\/span>\/<\/span>1024<\/span>\u00a0<<\/span>1<\/span>\u00a0then<\/span>\u00a03<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0when<\/span>\u00a0(<\/span>size<\/span>\u00a0*<\/span>\u00a08<\/span>)<\/span>\/<\/span>1024<\/span>\u00a0<<\/span>\u00a064<\/span>\u00a0then<\/span>\u00a04<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0when<\/span>\u00a0(<\/span>size<\/span>\u00a0*<\/span>\u00a08<\/span>)<\/span>\/<\/span>1024<\/span>\u00a0<<\/span>\u00a0100<\/span>\u00a0then<\/span>\u00a08<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0else<\/span>\u00a016<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0end<\/span>\u00a0RecommendedVLogs<\/span>
\n from<\/span>\u00a0qry<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0inner<\/span>\u00a0join<\/span>\u00a0sys<\/span>.<\/span>master_files<\/span>\u00a0smf<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0on<\/span>\u00a0db_name<\/i><\/span>(<\/span>smf<\/span>.<\/span>database_id<\/span>)<\/span>=<\/span>qry<\/span>.<\/span>name<\/span>
\n \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0and<\/span>\u00a0smf<\/span>.<\/span>file_id<\/span>\u00a0=<\/span>\u00a0qry<\/span>.<\/span>file_id<\/span>
\n where<\/span>\u00a0smf<\/span>.<\/span>type_desc<\/span>=<\/span>‘LOG’<\/span> <\/span><\/div>\n

\"vlogs<\/p>\n<\/blockquote>\n

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.<\/p>\n","protected":false},"excerpt":{"rendered":"

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……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2,143527],"tags":[5842],"coauthors":[6810],"class_list":["post-71388","post","type-post","status-publish","format-standard","hentry","category-blogs","category-database-administration-sql-server","tag-sql-monitor"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71388","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=71388"}],"version-history":[{"count":3,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71388\/revisions"}],"predecessor-version":[{"id":83071,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/71388\/revisions\/83071"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=71388"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=71388"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=71388"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=71388"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}