{"id":4682,"date":"2017-01-30T16:01:05","date_gmt":"2017-01-31T00:01:05","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/paul\/?p=4682"},"modified":"2017-01-31T11:36:48","modified_gmt":"2017-01-31T19:36:48","slug":"what-are-logmgr_reserve_append-waits","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/what-are-logmgr_reserve_append-waits\/","title":{"rendered":"What are LOGMGR_RESERVE_APPEND waits?"},"content":{"rendered":"

Last week I was sent an email question about the cause of LOGMGR_RESERVE_APPEND<\/em> waits, and in Monday’s Insider newsletter<\/a> I wrote a short explanation. It’s a very unusual wait to see as the highest wait on a server, and in fact it’s very unusual to see it at all.<\/p>\n

It happens when a thread is generating a log record and needs to write it into a log block, but there’s no space in the log to do so. The thread first tries to grow the log, and if it fails, and the database is in the simple recovery mode, then it waits for 1 second to see if log clearing\/truncation can happen in the meantime and free up some space. (Note that when I say ‘simple recovery mode’, this also includes a database in full or bulk_logged, but where a full backup has not been taken – i.e. the database is operating in what’s called pseudo-simple.)<\/p>\n

It’s the fact that the database needs to be in the simple recovery mode and have no space available that makes this wait type very unusual to see.<\/p>\n

An example call stack is on SQL Server 2014 (captured using this mechanism<\/a>):<\/p>\n

\r\nSOS_Task::PostWait+9e\r\nSOS_Task::Sleep+149\r\nSQLServerLogMgr::ReserveAndAppend+2cf\r\nXdesRMReadWrite::GenerateLogRec+615\r\nPageRef::InsertRows+2594\r\nHeapPageRef::Insert+45a\r\nHeapFragment::Update+3f7\r\nHeapDataSetSession::InsertRowInternal+3444\r\nDatasetSession::InsertRow+163\r\nRowsetNewSS::InsertRow+26\r\nCValRow::SetDataX+5b\r\nCEsExec::GeneralEval4+e7\r\nCQScanUpdateNew::GetRow+43d\r\nCQueryScan::GetRow+81\r\nCXStmtQuery::ErsqExecuteQuery+36d\r\nCXStmtDML::XretDMLExecute+31c\r\nCXStmtDML::XretExecute+ad\r\nCMsqlExecContext::ExecuteStmts<1,1>+427\r\nCMsqlExecContext::FExecute+a43\r\nCSQLSource::Execute+86c\r\n<\/pre>\n
Here’s a scenario that shows it happening. First I’ll create the database\u00a0with a fixed size log, set it to simple recovery mode, and create a table that will generate large log records for inserts:<\/p>\n
\r\nCREATE DATABASE [Company] ON PRIMARY (\r\n    NAME = N'Company_data',\r\n    FILENAME = N'D:\\SQLskills\\Company_data.mdf')\r\nLOG ON (\r\n    NAME = N'Company_log',\r\n    FILENAME = N'C:\\SQLskills\\Company_log.ldf',\r\n    SIZE = 2MB,\r\n    FILEGROWTH = 0MB);\r\nGO\r\nUSE [Company];\r\nGO\r\nALTER DATABASE [Company] SET RECOVERY SIMPLE;\r\nGO\r\nCREATE TABLE [BigRows] ([c1] INT IDENTITY, [c2] CHAR (8000) DEFAULT 'a');\r\nGO\r\n<\/pre>\n
And then in two other windows, run the following code:<\/p>\n
\r\nUSE [Company];\r\nGO\r\nSET NOCOUNT ON;\r\nGO\r\n\r\nWHILE (1 = 1)\r\nBEGIN\r\n\tINSERT INTO [BigRows] DEFAULT VALUES;\r\nEND;\r\nGO\r\n<\/pre>\n
And within a few seconds, you’ll see LOGMGR_RESERVE_APPEND<\/em> waits happening (using my waits script<\/a>). Here’s an example (with a few columns removed for brevity):<\/p>\n
\r\nWaitType                       WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S Help\/Info URL\r\n------------------------------ --------- ---------- --------- -------- -------- -------------------------------------------------------------------\r\nLOGMGR_RESERVE_APPEND          26        50.78      0.9847    0.9847   0.0000   https:\/\/www.sqlskills.com\/help\/waits\/LOGMGR_RESERVE_APPEND\r\nPREEMPTIVE_OS_FLUSHFILEBUFFERS 954       22.14      0.0116    0.0116   0.0000   https:\/\/www.sqlskills.com\/help\/waits\/PREEMPTIVE_OS_FLUSHFILEBUFFERS\r\nWRITELOG                       131625    21.63      0.0001    0.0001   0.0000   https:\/\/www.sqlskills.com\/help\/waits\/WRITELOG\r\nPAGEIOLATCH_UP                 5841      3.37       0.0003    0.0003   0.0000   https:\/\/www.sqlskills.com\/help\/waits\/PAGEIOLATCH_UP\r\n<\/pre>\n
So if you ever see these waits, look for databases using the simple recovery mode where the log is set to have zero or very tiny autogrowth.<\/p>\n
Enjoy!<\/p>\n","protected":false},"excerpt":{"rendered":"
Last week I was sent an email question about the cause of LOGMGR_RESERVE_APPEND waits, and in Monday’s Insider newsletter I wrote a short explanation. It’s a very unusual wait to see as the highest wait on a server, and in fact it’s very unusual to see it at all. It happens when a thread is […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[48,101],"tags":[],"class_list":["post-4682","post","type-post","status-publish","format-standard","hentry","category-inside-the-storage-engine","category-wait-stats"],"yoast_head":"\nWhat are LOGMGR_RESERVE_APPEND waits? - 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\/what-are-logmgr_reserve_append-waits\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"What are LOGMGR_RESERVE_APPEND waits? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"Last week I was sent an email question about the cause of LOGMGR_RESERVE_APPEND waits, and in Monday’s Insider newsletter I wrote a short explanation. It’s a very unusual wait to see as the highest wait on a server, and in fact it’s very unusual to see it at all. It happens when a thread is […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/what-are-logmgr_reserve_append-waits\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2017-01-31T00:01:05+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-01-31T19:36:48+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\/what-are-logmgr_reserve_append-waits\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/what-are-logmgr_reserve_append-waits\/\",\"name\":\"What are LOGMGR_RESERVE_APPEND waits? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2017-01-31T00:01:05+00:00\",\"dateModified\":\"2017-01-31T19:36:48+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/what-are-logmgr_reserve_append-waits\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/what-are-logmgr_reserve_append-waits\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/what-are-logmgr_reserve_append-waits\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"What are LOGMGR_RESERVE_APPEND waits?\"}]},{\"@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":"What are LOGMGR_RESERVE_APPEND waits? - 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\/what-are-logmgr_reserve_append-waits\/","og_locale":"en_US","og_type":"article","og_title":"What are LOGMGR_RESERVE_APPEND waits? - Paul S. Randal","og_description":"Last week I was sent an email question about the cause of LOGMGR_RESERVE_APPEND waits, and in Monday’s Insider newsletter I wrote a short explanation. It’s a very unusual wait to see as the highest wait on a server, and in fact it’s very unusual to see it at all. It happens when a thread is […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/what-are-logmgr_reserve_append-waits\/","og_site_name":"Paul S. Randal","article_published_time":"2017-01-31T00:01:05+00:00","article_modified_time":"2017-01-31T19:36:48+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\/what-are-logmgr_reserve_append-waits\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/what-are-logmgr_reserve_append-waits\/","name":"What are LOGMGR_RESERVE_APPEND waits? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2017-01-31T00:01:05+00:00","dateModified":"2017-01-31T19:36:48+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/what-are-logmgr_reserve_append-waits\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/what-are-logmgr_reserve_append-waits\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/what-are-logmgr_reserve_append-waits\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"What are LOGMGR_RESERVE_APPEND waits?"}]},{"@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\/4682","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=4682"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/4682\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=4682"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=4682"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=4682"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}