BACKUPBUFFER Wait Type In SQL Server

A lot of times the DBA will come across one wait type and that is the BACKUPBUFFER. The BACKUPBUFFER wait type will occur every time there is a backup happening in your SQL Server instance. It doesn’t matter if the backup operation is written on a tape mount or not, the SQL Server will still generate the BACKUPBUFFER wait type. Let’s look in detail at the BACKUPBUFFER wait type.

What is the BACKUPBUFFER Wait Type?

The BACKUPBUFFER wait type is generated whenever there is a backup operation happening in an SQL Server instance. When a backup runs, the SQL Server allocates a buffer for the backup process. The wait type will occur regardless of any backup method used. It can be a full, differential, or log backup. Once the buffer allocates, the buffer gets filled with data in your database and will be written to the backup file.

Additionally, the backup buffers with regards to amount and size are automatically calculated by SQL Server. However, the values can be configured manually by passing parameters to the backup or restore command.

How to check the backup buffer amount and size?

To check the backup buffer amount and size, we will first have to enable two trace flags, 3213 and 3605. These two trace flags will output backup and restore details into the SQL Server error log.

DBCC TRACEON (3213);
DBCC TRACEON (3605);

Note: Enable these trace flags in SQL Server under a DBA’s guidance or Microsoft support.

We now have an idea of how the backup operation pushes the information inside the SQL Server error log. Additionally, we also got the idea that the SQL Server backup process uses a buffer to store data in the backup file.

What we need to understand is, when the BACKUPBUFFER wait type occurs in an SQL Server instance? The BACKUPBUFFER wait type comes in whenever the buffers are not available to write directly in the backup file. The backup process will wait until the buffer is available. When the backup process waits, the BACKUPBUFFER wait type will keep generating. The wait type shows up until the buffer is directly available.

Sys.dm_os_wait_stats

You can check the wait type details in the sys.dm_os_wait_stats DMV.

select * from sys.dm_os_wait_stats where wait_type ='BACKUPBUFFER'

How to Lower BACKUPBUFFER Waits?

The BACKUPBUFFER wait type is not a concern of performance issues in an SQL Server instance. Whenever there is a backup operation running in your SQL Server machine, the BACKUPBUFFER wait type can come up. We can commonly lower the BACKUPBUFFER wait times by adding more buffers for the backup operation. This alteration can be done by specifying the BUFFERCOUNT in the backup command.

Note: Making a change in the BUFFERCOUNT can result in out-of-memory problems in case large buffers get generated. Use the option under a DBA’s guidance.

Bottomline

The BACKUPBUFFER wait type occurs whenever a backup runs in an SQL Server instance. The value of this wait type can increase when the backup operation is waiting for more buffers to allocate. In most cases, the BACKUPBUFFER wait type is not a performance overhead.