LOGMGR_FLUSH

(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)

(Back to main page…)

Description:

This wait type has been repurposed in SQL Server 2016 onward and is when a thread is generating a log record and needs to wait for the current log flush to complete. I expect this is related to the work done for SQL Server 2016 that increased the number of background ‘logwriter’ threads up to 4 (and then up 8 in SQL Server 2019).

(Books Online description: “Identified for informational purposes only. Not supported. Future compatibility is not guaranteed.”)

Questions/comments on this wait type? Click here to send Paul an email, especially if you have any information to add to this topic.

Added in SQL Server version:

Pre-2005/2005

Removed in SQL Server version:

N/A

Extended Events wait_type value:

The map_key value in sys.dm_xe_map_values is 241 in 2008 and 2008 R2, 248 in 2012, and 255 in 2014 RTM. After 2014 RTM, you must check the DMV to get the latest value as some map_key values have changed in later builds.

Other information:

I have not seen this wait type be a noticeable contention point.

Known occurrences in SQL Server (list number matches call stack list):

1. Waiting to write a log record, in this case, while preallocating space for the Persistent Version Store that underpins Accelerated Database Recovery
2. Waiting to write a log record, in this case, while in a system transaction that is performing a page split while inserting a recording into an index
3. Waiting to write a log record, in this case, while committing a transaction

And many other similar call stacks involving log record generation.

Abbreviated call stacks (list number matches known occurrences list):

1. SOS_Task::PostWait+0x6f
   SOS_Task::Sleep+0x140
   LogAppendContext::BackOff+0x18e
   SQLServerLogMgr::AppendLogRequest+0x332
   SQLServerLogMgr::ReserveAndAppend+0x21b
   XdesRMReadWrite::GenerateLogRec+0xba1
   PageRef::SetupPageHeaderPreAllocation+0x1b7
   SetupPageHeaderPreAllocation+0x72
   AllocationReq::AllocateExtent+0x375
   AllocationReq::AllocatePages+0x151c
   AllocationReq::Allocate+0x144
   ExtentAllocator::PreAllocateExtents+0x4da
   ExtentAllocatorSingleAlloc::PreAllocate+0x9d
   ExtentAllocatorSingleAlloc::AllocateExtents+0xa0
   VersionStoreTableAccess::AllocatePages+0x1a1
   PersistVerStore::PreAllocatePages+0x42f
   PreAllocatePVSPages+0x4fa
   SOS_Task::Param::Execute+0x232
   SOS_Scheduler::RunTask+0xb5
2. SOS_Task::PostWait+0x6f
   SOS_Task::Sleep+0x140
   LogAppendContext::BackOff+0x18e
   SQLServerLogMgr::AppendLogRequest+0x332
   SQLServerLogMgr::ReserveAndAppend+0x21b
   XdesRMReadWrite::GenerateLogRec+0xba1
   PageRef::SetInSysXactFlag+0x7e1
   SMOLockOrder::SetInSysXactFlagForPage+0x1b3
   BTreeMgr::AddParentToSMOLockOrder+0x29
   IndexPageManager::GetSMOLockOrderForSplit+0x5a
   SplitPage+0x9e0
   IndexDataSetSession::InsertSmallRecord+0x2025
   IndexDataSetSession::InsertRowInternal+0x2074
   DatasetSession::InsertRow+0x2b9
   RowsetNewSS::InsertRow+0x32
   CValRow::SetDataX+0x5b
   CEsExec::GeneralEval+0x1d9
   CQScanUpdateNew::GetRow+0x502
   CQScanLightProfileNew::GetRow+0x19
   CQueryScan::GetRow+0x80
   CXStmtQuery::ErsqExecuteQuery+0x3de
   CXStmtDML::XretDMLExecute+0x48c
3. SOS_Task::PostWait+0x6f
   SOS_Task::Sleep+0x140
   LogAppendContext::BackOff+0x18e
   SQLServerLogMgr::AppendLogRequest+0x332
   SQLServerLogMgr::ReserveAndAppend+0x21b
   XdesRMReadWrite::GenerateLogRec+0xba1
   XdesRMReadWrite::LogEndXact+0x5f2
   XdesRMFull::CommitInternal+0x4b8
   XactRM::SinglePhaseCommit+0x1ed
   XactRM::CommitInternal+0x8d3
   FullXactImp::Commit+0x3fe
   CMsqlXactInternalReadWrite::Commit+0x26
   CMsqlXactImp::Commit+0x1a3
   CXStmtXactEnd::XretExecute+0xf9
   CMsqlExecContext::ExecuteStmts<1,1>+0x8f8
   CMsqlExecContext::FExecute+0x946
   CSQLSource::Execute+0xb9f