(Republishing, or using this info in a commercial product/website, is prohibited without permission. All other uses are permitted. If in doubt, please ask.)
Description:
This wait type is 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 (or operating in pseudo-simple mode), then it waits for 1 second to see if log clearing/truncation can happen in the meantime and free up some space.
(Books Online description: “Occurs when a task is waiting to see whether log truncation frees up log space to enable the task to write a new log record. Consider increasing the size of the log file(s) for the affected database to reduce this wait.”)
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 240 in 2008 and 2008 R2, 247 in 2012, and 254 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 is very unusual to see, and even more unusual to see as one of the highest wait types on an instance. If you see this wait type, look for a database in the simple recovery mode where the log file autogrowth has been set to zero, or to a very small value. Make the autogrowth value larger, and grow the log file so there is enough space for log records to be stored while log clearing is occurring.
See this blog post for an example scenario involving this wait type.
Known occurrences in SQL Server (list number matches call stack list):
- Waiting for space to become available while creating a log record
And many, many more similar call stacks involving log record creation.
Abbreviated call stacks (list number matches known occurrences list):
- 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