{"id":536,"date":"2011-09-13T11:52:00","date_gmt":"2011-09-13T11:52:00","guid":{"rendered":"\/blogs\/paul\/post\/Initial-VLF-sequence-numbers-and-default-log-file-size.aspx"},"modified":"2013-01-27T09:20:08","modified_gmt":"2013-01-27T17:20:08","slug":"initial-vlf-sequence-numbers-and-default-log-file-size","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/initial-vlf-sequence-numbers-and-default-log-file-size\/","title":{"rendered":"Initial VLF sequence numbers and default log file size"},"content":{"rendered":"

\nWe're teaching on-site with a financial client in New York for two weeks and in a session this morning on log file architecture I was asked why the VLF (virtual log file) sequence numbers in a new database don't start at one.<\/font>\n<\/p>\n

\nHere's an example:<\/font>\n<\/p>\n

\n

\n\tCREATE DATABASE foo;
\n\tGO
\n\tDBCC LOGINFO ('foo');
\n\tGO<\/font>\n\t<\/p>\n

\n\tFileId  FileSize  StartOffset FSeqNo Status  Parity  CreateLSN
\n\t——- ——— ———– —— ——- ——- ——————
\n\t2       253952    8192        137    2       64      0
\n\t2       253952    262144      0      0       0       0<\/font>\n\t<\/p>\n<\/blockquote>\n

\nWhy is the first sequence number (FSeqNo<\/font>) 137 and not 1? (For an explanation of the various fields in the output of the undocumented DBCC LOGINFO<\/font>, see the post <\/font>Inside the Storage Engine: More on the circular nature of the log<\/font><\/a>.)<\/font>\n<\/p>\n

\nThe answer is that it's one more than the highest VLF sequence number currently in the model<\/font> database. Let's see:<\/font>\n<\/p>\n

\n

\n\tDBCC LOGINFO ('model');
\n\tGO<\/font>\n\t<\/p>\n

\n\tFileId  FileSize  StartOffset FSeqNo Status  Parity  CreateLSN
\n\t——- ——— ———– —— ——- ——- ——————
\n\t2       253952    8192        136    2       128     0
\n\t2       262144    262144      135    0       128     0<\/font>\n\t<\/p>\n<\/blockquote>\n

\nThe highest FSeqNo<\/font> in the output above is 136, which accounts for the 137 in the new user database.<\/font>\n<\/p>\n

\nNow I'll prove it by increasing the sequence number in the model<\/font> database:<\/font>\n<\/p>\n

\n

\n\tUSE model;
\n\tGO
\n\tCREATE TABLE BigRows (c1 INT IDENTITY, c2 CHAR (8000) DEFAULT 'a');
\n\tGO
\n\tINSERT INTO BigRows DEFAULT VALUES;
\n\tGO 50
\n\tDBCC LOGINFO;
\n\tGO<\/font>\n\t<\/p>\n

\n\tFileId  FileSize  StartOffset FSeqNo Status  Parity  CreateLSN
\n\t——- ——— ———– —— ——- ——- ——————
\n\t2       253952    8192        136    0       128     0
\n\t2       262144    262144      137    2       128     0<\/font>\n\t<\/p>\n<\/blockquote>\n

\nAnd now I'll create another user database and the first VLF sequence number should be 138:<\/font>\n<\/p>\n

\n

\n\tCREATE DATABASE foo2;
\n\tGO
\n\tDBCC LOGINFO ('foo2');
\n\tGO<\/font>\n\t<\/p>\n

\n\tFileId  FileSize  StartOffset FSeqNo Status  Parity  CreateLSN
\n\t——- ——— ———– —— ——- ——- ——————
\n\t2       253952    8192        138    2       64      0
\n\t2       360448    262144      0      0       0       0<\/font>\n\t<\/p>\n<\/blockquote>\n

\nAnd it is – proven!<\/font>\n<\/p>\n

\nThe eagle-eyed among you may notice that the log file size of the database foo2<\/font> I just created is larger than the size of the foo<\/font> database I initially created. This is because the default log file size is the higher of 0.5MB or 25% of the sum of the data files created. In this case, the data file is copied from the model<\/font> database, and I made it larger when I created the BigRows<\/font> table, so the log file of the new database has to be slightly larger.<\/font>\n<\/p>\n

\nOne question that came up in the post comments – why is the highest VLF sequence number in my model database 136? No particular reason except I've done a bunch of things in model which generated transaction log and so the VLF sequence numbers have increased. It's almost certain to be different in your SQL Server instances for that reason.<\/font>\n<\/p>\n

\nNext up tomorrow – results from the index count survey from last month.<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

We're teaching on-site with a financial client in New York for two weeks and in a session this morning on log file architecture I was asked why the VLF (virtual log file) sequence numbers in a new database don't start at one. Here's an example: CREATE DATABASE foo; GO DBCC LOGINFO ('foo'); GO FileId  FileSize  […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[48,62,98],"tags":[],"class_list":["post-536","post","type-post","status-publish","format-standard","hentry","category-inside-the-storage-engine","category-on-disk-structures","category-transaction-log"],"yoast_head":"\nInitial VLF sequence numbers and default log file size - 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\/initial-vlf-sequence-numbers-and-default-log-file-size\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Initial VLF sequence numbers and default log file size - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"We're teaching on-site with a financial client in New York for two weeks and in a session this morning on log file architecture I was asked why the VLF (virtual log file) sequence numbers in a new database don't start at one. Here's an example: CREATE DATABASE foo; GO DBCC LOGINFO ('foo'); GO FileId  FileSize  […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/initial-vlf-sequence-numbers-and-default-log-file-size\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2011-09-13T11:52:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2013-01-27T17:20:08+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=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/initial-vlf-sequence-numbers-and-default-log-file-size\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/initial-vlf-sequence-numbers-and-default-log-file-size\/\",\"name\":\"Initial VLF sequence numbers and default log file size - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2011-09-13T11:52:00+00:00\",\"dateModified\":\"2013-01-27T17:20:08+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/initial-vlf-sequence-numbers-and-default-log-file-size\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/initial-vlf-sequence-numbers-and-default-log-file-size\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/initial-vlf-sequence-numbers-and-default-log-file-size\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Initial VLF sequence numbers and default log file size\"}]},{\"@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":"Initial VLF sequence numbers and default log file size - 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\/initial-vlf-sequence-numbers-and-default-log-file-size\/","og_locale":"en_US","og_type":"article","og_title":"Initial VLF sequence numbers and default log file size - Paul S. Randal","og_description":"We're teaching on-site with a financial client in New York for two weeks and in a session this morning on log file architecture I was asked why the VLF (virtual log file) sequence numbers in a new database don't start at one. Here's an example: CREATE DATABASE foo; GO DBCC LOGINFO ('foo'); GO FileId  FileSize  […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/initial-vlf-sequence-numbers-and-default-log-file-size\/","og_site_name":"Paul S. Randal","article_published_time":"2011-09-13T11:52:00+00:00","article_modified_time":"2013-01-27T17:20:08+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/initial-vlf-sequence-numbers-and-default-log-file-size\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/initial-vlf-sequence-numbers-and-default-log-file-size\/","name":"Initial VLF sequence numbers and default log file size - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2011-09-13T11:52:00+00:00","dateModified":"2013-01-27T17:20:08+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/initial-vlf-sequence-numbers-and-default-log-file-size\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/initial-vlf-sequence-numbers-and-default-log-file-size\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/initial-vlf-sequence-numbers-and-default-log-file-size\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Initial VLF sequence numbers and default log file size"}]},{"@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\/536","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=536"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/536\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=536"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=536"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=536"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}