What are LOGMGR_RESERVE_APPEND waits?

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 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.)

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.

An example call stack is on SQL Server 2014 (captured using this mechanism):

SOS_Task::PostWait+9e
SOS_Task::Sleep+149
SQLServerLogMgr::ReserveAndAppend+2cf
XdesRMReadWrite::GenerateLogRec+615
PageRef::InsertRows+2594
HeapPageRef::Insert+45a
HeapFragment::Update+3f7
HeapDataSetSession::InsertRowInternal+3444
DatasetSession::InsertRow+163
RowsetNewSS::InsertRow+26
CValRow::SetDataX+5b
CEsExec::GeneralEval4+e7
CQScanUpdateNew::GetRow+43d
CQueryScan::GetRow+81
CXStmtQuery::ErsqExecuteQuery+36d
CXStmtDML::XretDMLExecute+31c
CXStmtDML::XretExecute+ad
CMsqlExecContext::ExecuteStmts<1,1>+427
CMsqlExecContext::FExecute+a43
CSQLSource::Execute+86c

Here’s a scenario that shows it happening. First I’ll create the database with a fixed size log, set it to simple recovery mode, and create a table that will generate large log records for inserts:

CREATE DATABASE [Company] ON PRIMARY (
    NAME = N'Company_data',
    FILENAME = N'D:\SQLskills\Company_data.mdf')
LOG ON (
    NAME = N'Company_log',
    FILENAME = N'C:\SQLskills\Company_log.ldf',
    SIZE = 2MB,
    FILEGROWTH = 0MB);
GO
USE [Company];
GO
ALTER DATABASE [Company] SET RECOVERY SIMPLE;
GO
CREATE TABLE [BigRows] ([c1] INT IDENTITY, [c2] CHAR (8000) DEFAULT 'a');
GO

And then in two other windows, run the following code:

USE [Company];
GO
SET NOCOUNT ON;
GO

WHILE (1 = 1)
BEGIN
	INSERT INTO [BigRows] DEFAULT VALUES;
END;
GO

And within a few seconds, you’ll see LOGMGR_RESERVE_APPEND waits happening (using my waits script). Here’s an example (with a few columns removed for brevity):

WaitType                       WaitCount Percentage AvgWait_S AvgRes_S AvgSig_S Help/Info URL
------------------------------ --------- ---------- --------- -------- -------- -------------------------------------------------------------------
LOGMGR_RESERVE_APPEND          26        50.78      0.9847    0.9847   0.0000   https://www.sqlskills.com/help/waits/LOGMGR_RESERVE_APPEND
PREEMPTIVE_OS_FLUSHFILEBUFFERS 954       22.14      0.0116    0.0116   0.0000   https://www.sqlskills.com/help/waits/PREEMPTIVE_OS_FLUSHFILEBUFFERS
WRITELOG                       131625    21.63      0.0001    0.0001   0.0000   https://www.sqlskills.com/help/waits/WRITELOG
PAGEIOLATCH_UP                 5841      3.37       0.0003    0.0003   0.0000   https://www.sqlskills.com/help/waits/PAGEIOLATCH_UP

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.

Enjoy!