SQL SERVER – BACKUPIO, BACKUPBUFFER – Wait Type – Day 14 of 28

Backup is the most important task for any database admin. Your data is at risk if you are not performing database backup. Honestly, I have seen many DBAs who know how to take backups but do not know how to restore it. (Sigh!)

In this blog post we are going to discuss about one of my real experiences with one of my clients – BACKUPIO. When I started to deal with it, I really had no idea how to fix the issue. However, after fixing it at two places, I think I know why this is happening but at the same time, I am not sure the fix is the best solution. The reality is that the fix is not a solution but a workaround (which is not optimal, but get your things done).

From Book On-Line:

BACKUPIO
Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount.

BACKUPBUFFER
Occurs when a backup task is waiting for data, or is waiting for a buffer in which to store data. This type is not typical, except when a task is waiting for a tape mount.

BACKUPIO and BACKUPBUFFER Explanation:

This wait stats will occur when you are taking the backup on the tape or any other extremely slow backup system.

Reducing BACKUPIO and BACKUPBUFFER wait:

In my recent consultancy, backup on tape was very slow probably because the tape system was very old. During the time when I explained this wait type reason in the consultancy, the owners immediately decided to replace the tape drive with an alternate system. They had a small SAN enclosure not being used on side, which they decided to re-purpose. After a week, I had received an email from their DBA, saying that the wait stats have reduced drastically.

At another location, my client was using a third party tool (please don’t ask me the name of the tool) to take backup. This tool was compressing the backup along with taking backup. I have had a very good experience with this tool almost all the time except this one sparse experience. When I tried to take backup using the native SQL Server compressed backup, there was a very small value on this wait type and the backup was much faster. However, when I attempted with the third party backup tool, this value was very high again and was taking much more time. The third party tool had many other features but the client was not using these features.

We end up using the native SQL Server Compressed backup and it worked very well.

If I get to see this higher in my future consultancy, I will try to understand this wait type much more in detail and so probably I would able to come to some solid solution.

Read all the post in the Wait Types and Queue series.

Note: The information presented here is from my experience and there is no way that I claim it to be accurate. I suggest reading Book OnLine for further clarification. All the discussion of Wait Stats in this blog is generic and varies from system to system. It is recommended that you test this on a development server before implementing it to a production server.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

4 thoughts on “SQL SERVER – BACKUPIO, BACKUPBUFFER – Wait Type – Day 14 of 28

  1. Pingback: SQL SERVER – Weekly Series – Memory Lane – #016 « SQL Server Journey with SQL Authority

  2. Hello Sir – thank you for your informative blog… I am a frequent visitor. Please moderate this post and delete at your pleasure. I say this because I am using a 3rd-party tool to compress my backups and am experiencing the BACKUPIO waittype. My curiosity has peaked. :)

    Your prior experience wasn’t with Red Gate’s HyperBac product, was it? Your reply will not impact my decision to remove this product from my shop… it is already out of here. But it will help give me some insight as to why I struggle with this waittype even though my host is SAN attached. Many Thanks! Tony

  3. Hi Sir,
    I have a question here. We have a production server with 1800 databases in it. And we take differential backup everyday of all of them in sequential order which is consuming a lot of time since the backup order is sequential. I am planning to run backup parallely for 100 databases at a time.
    But i see the waittype BACKUPIO with wait_time_ms as almost 30 minutes for now.
    Do you think parallel backup is going to affect the performance in any manner?
    Disk drives has no space issue and cpu is very low when we run the backups and each backup takes less than a minute. Appreciate your quick response.

  4. Pingback: SQL SERVER – 28 Links for Learning SQL Wait Stats from Beginning | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s