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

LOGBUFFER

(Back to main page…)

Description:

This wait type is when a thread is waiting for a free log buffer in the log cache, so the current log block that is in the process of ending (because it reached the maximum 60KB size or because a transaction wrote an LOP_COMMIT_XACT log record) can be copied into the log cache and then subsequently written to disk.

(Books Online description: “Occurs when a task is waiting for space in the log buffer to store a log record. Consistently high values may indicate that the log devices cannot keep up with the amount of log being generated by the server.”)

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 202 in 2008 and 2008 R2, 209  in 2012, and 216 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:

This wait type can become prevalent, along with WRITELOG, if the amount of transaction log and frequency of log block flushes is overwhelming the log management system and/or the I/O subsystem on which the transaction log resides. It will also appear after a database has been brought online and the log cache is growing from its initial size of 1 log buffer up to its maximum of 128 log buffers per database. See the WRITELOG wait for more information on how to alleviate this wait type if it is prevalent.

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

  1. Waiting for a log buffer (in this case, because a log block filled up while logging an insert into a heap)
  2. Waiting for a log buffer (in this case, because a log blocked ended due to a system transaction committing after allocating a new page)
  3. Waiting for a log buffer (in this case, because a log blocked ended due to a system transaction committing after splitting a page)

And many, many more similar call stacks.

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

  1. SOS_Task::PostWait+90
    EventInternal<SuspendQueueSLock>::Wait+1f9
    LC::WaitForMemBlk+ac
    SQLServerLogMgr::AppendLogRequest+3c1
    SQLServerLogMgr::ReserveAndAppend+74
    XdesRMReadWrite::GenerateLogRec+5ac
    ParNestedXdes::GenerateLogRec+be
    PageRef::InsertRows+242b
    HeapPageRef::Insert+432
    HeapFragment::Update+3f7
    HeapDataSetSession::InsertRowInternal+3320
    DatasetSession::InsertRow+163
    RowsetNewSS::InsertRow+26
    CValRow::SetDataX+5b
    CQScanUpdateNew::GetRow+3f7
    CQueryScan::GetRow+81
    CXStmtQuery::ErsqExecuteQuery+54a
    CXStmtDML::XretDMLExecute+2f3
    CXStmtDML::XretExecute+ad
    CMsqlExecContext::ExecuteStmts<1,1>+400
    CMsqlExecContext::FExecute+a33
  2. SOS_Task::PostWait+90
    EventInternal<SuspendQueueSLock>::Wait+1f9
    LC::WaitForMemBlk+ac
    SQLServerLogMgr::AppendLogRequest+3c1
    SQLServerLogMgr::ReserveAndAppend+74
    XdesRMReadWrite::GenerateLogRec+5ac
    XdesRMReadWrite::LogEndXact+592
    XdesRMSystem::Commit+11f
    SystemXactImp::Commit+a4
    HeapFragment::AllocateNewPageToConstructFragment+87a
    HeapDataSetSession::InsertRowInternal+329d
    DatasetSession::InsertRow+163
    RowsetNewSS::InsertRow+26
    CValRow::SetDataX+5b
    CQScanUpdateNew::GetRow+3f7
    CQueryScan::GetRow+81
    CXStmtQuery::ErsqExecuteQuery+54a
    CXStmtDML::XretDMLExecute+2f3
    CXStmtDML::XretExecute+ad
    CMsqlExecContext::ExecuteStmts<1,1>+400
    CMsqlExecContext::FExecute+a33
  3. SOS_Task::PostWait+90
    EventInternal<SuspendQueueSLock>::Wait+1f9
    LC::WaitForMemBlk+ac
    SQLServerLogMgr::AppendLogRequest+3c1
    SQLServerLogMgr::ReserveAndAppend+74
    XdesRMReadWrite::GenerateLogRec+5ac
    XdesRMReadWrite::LogEndXact+592
    XdesRMSystem::Commit+11f
    SystemXactImp::Commit+a4
    AutoSystemXact::Commit+15
    SplitPage+d1c
    IndexDataSetSession::InsertSmallRecord+1b5c
    IndexDataSetSession::InsertRowInternal+2d11
    DatasetSession::InsertRow+163
    RowsetNewSS::InsertRow+26
    CValRow::SetDataX+5b
    CQScanUpdateNew::GetRow+3f7
    CQueryScan::GetRow+81
    CXStmtQuery::ErsqExecuteQuery+54a
    CXStmtDML::XretDMLExecute+2f3
    CXStmtDML::XretExecute+ad