{"id":957,"date":"2009-02-09T18:18:00","date_gmt":"2009-02-09T18:18:00","guid":{"rendered":"\/blogs\/paul\/post\/Performance-impact-of-lots-of-VLFs-in-the-transaction-log.aspx"},"modified":"2018-05-09T08:48:11","modified_gmt":"2018-05-09T15:48:11","slug":"performance-impact-of-lots-of-vlfs-in-the-transaction-log","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-impact-of-lots-of-vlfs-in-the-transaction-log\/","title":{"rendered":"Performance impact of lots of VLFs in the transaction log"},"content":{"rendered":"<p><span style=\"font-family: verdana, geneva; font-size: small;\">One of the things I mentioned in my recent TechNet Magazine article on <\/span><a href=\"https:\/\/technet.microsoft.com\/en-us\/library\/2009.02.logging.aspx\"><span style=\"font-family: verdana, geneva; font-size: small;\">Understanding Logging and Recovery<\/span><\/a><span style=\"font-family: verdana, geneva; font-size: small;\"> was the need to manage the transaction log so the number of VLFs (virtual log files) does not get too large (with too large being more than, say, 100 VLFs). Linchi Shea (a fellow MVP) has just posted some performance numbers comparing insert\/update\/delete performance between a database with 16 VLFs and one with 20000 VLFs, representing a poorly managed transaction log. You can see his blog post here (2018: sadly it&#8217;s been taken down &#8211; sorry!)<\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">For those wishing to take charge of unruly transaction logs, checkout Kimberly&#8217;s blog posts &#8211; starting with <\/span><a href=\"http:\/\/www.sqlskills.com\/blogs\/kimberly\/transaction-log-vlfs-too-many-or-too-few\/\"><span style=\"font-family: verdana, geneva; font-size: small;\">this one<\/span><\/a><span style=\"font-family: verdana, geneva; font-size: small;\">. And if you want to quickly know how many VLFs your transaction log has, use the undocumented <span style=\"font-family: 'courier new', courier;\">DBCC LOGINFO<\/span> command &#8211; the number of lines of output is the number of VLFs you have.<\/span><\/p>\n<p><span style=\"font-family: verdana, geneva; font-size: small;\">Next posts coming up will be photos &#8211; we&#8217;re in Bangkok right now and I&#8217;ve got 3 <a href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/category\/where-in-the-world-are-paul-and-kimberly\/\">Where In The World Are Paul and Kimberly<\/a> posts queued up &#8211; St. Lucia, Hyderabad, and Bangkok.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>One of the things I mentioned in my recent TechNet Magazine article on Understanding Logging and Recovery was the need to manage the transaction log so the number of VLFs (virtual log files) does not get too large (with too large being more than, say, 100 VLFs). Linchi Shea (a fellow MVP) has just posted [&hellip;]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[31,52,66,98],"tags":[],"class_list":["post-957","post","type-post","status-publish","format-standard","hentry","category-database-maintenance","category-involuntary-dba","category-performance-tuning","category-transaction-log"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Performance impact of lots of VLFs in the transaction log - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-impact-of-lots-of-vlfs-in-the-transaction-log\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Performance impact of lots of VLFs in the transaction log - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"One of the things I mentioned in my recent TechNet Magazine article on Understanding Logging and Recovery was the need to manage the transaction log so the number of VLFs (virtual log files) does not get too large (with too large being more than, say, 100 VLFs). Linchi Shea (a fellow MVP) has just posted [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-impact-of-lots-of-vlfs-in-the-transaction-log\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2009-02-09T18:18:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2018-05-09T15:48:11+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-impact-of-lots-of-vlfs-in-the-transaction-log\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-impact-of-lots-of-vlfs-in-the-transaction-log\/\",\"name\":\"Performance impact of lots of VLFs in the transaction log - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2009-02-09T18:18:00+00:00\",\"dateModified\":\"2018-05-09T15:48:11+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-impact-of-lots-of-vlfs-in-the-transaction-log\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-impact-of-lots-of-vlfs-in-the-transaction-log\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-impact-of-lots-of-vlfs-in-the-transaction-log\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Performance impact of lots of VLFs in the transaction log\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Performance impact of lots of VLFs in the transaction log - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-impact-of-lots-of-vlfs-in-the-transaction-log\/","og_locale":"en_US","og_type":"article","og_title":"Performance impact of lots of VLFs in the transaction log - Paul S. Randal","og_description":"One of the things I mentioned in my recent TechNet Magazine article on Understanding Logging and Recovery was the need to manage the transaction log so the number of VLFs (virtual log files) does not get too large (with too large being more than, say, 100 VLFs). Linchi Shea (a fellow MVP) has just posted [&hellip;]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-impact-of-lots-of-vlfs-in-the-transaction-log\/","og_site_name":"Paul S. Randal","article_published_time":"2009-02-09T18:18:00+00:00","article_modified_time":"2018-05-09T15:48:11+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-impact-of-lots-of-vlfs-in-the-transaction-log\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-impact-of-lots-of-vlfs-in-the-transaction-log\/","name":"Performance impact of lots of VLFs in the transaction log - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2009-02-09T18:18:00+00:00","dateModified":"2018-05-09T15:48:11+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-impact-of-lots-of-vlfs-in-the-transaction-log\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/performance-impact-of-lots-of-vlfs-in-the-transaction-log\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/performance-impact-of-lots-of-vlfs-in-the-transaction-log\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Performance impact of lots of VLFs in the transaction log"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/957","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=957"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/957\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=957"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=957"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=957"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}