• Question

  • Is it normal for lastwaittype ASYNC_IO_COMPLETION to linger around for the entire duration of of a backup? We're trouble shooting a backup job that is taking longer than what we are used to seeing. And one thing that we picked up on is that lastwaittype of ASYNC_IO_COMPLETION appears on the spid running the backup and the waittime on it never goes down and continues to tick up for the entire duration of the backup. Is this a sure sign of something wrong on the disk that the backup is writing to? Or can it also have something to do with the database itself?

    Tuesday, November 12, 2013 2:38 AM

All replies

  • Hi,

    Yes ASYNC_IO_COMPLETION wait type is normal to be seen when backups are executing. 

    Please use the below script to get the current wait type and also the progress of the backup through percent_complete column.

    select session_id, blocking_session_id, db_name(database_id) as [Database], command, percent_complete, wait_type,wait_time, wait_resource, scheduler_id, Qry.text from sys.dm_exec_requests req cross apply sys.fn_get_sql(req.sql_handle) as Qry where req.session_id>50 and command = 'BACKUP DATABASE'

    During the backup if you ant to check the disk activity then you can use perfmon with average disk queue length counter.

    Generally as the database grows in size gradually the time required for taking backup also increases. If the backups are going to SAN storage disks then you can get the disk level performance details from your storage team.

    Thanks and Regards Alankar Chakravorty MCITP Database Administrator SQL Server 2008 MCITP Database Administrator SQL Server 2005

    Tuesday, November 12, 2013 2:49 AM
  • Thank you for that query. Although that is helpful, that is not what I need help with. What I need to verify is this. We have a consultant who is suggesting that our long running backup is surely tied to a poorly set up storage/disk/infrastructure because we're seeing this ASYNC_IO_COMPLETION lastwaittype. But you're saying that isn't true. This lastwaittype is normal for a backup job. If that is the case what should we be looking at the understand why we're seeing an increase in the backup time? The database size hasn't changed significantly. The problem may well after all be 100% related to infrastructure, but I want to know if ASYNC_IO_COMPLETION lastwaittype is the thing to point at for that?
    Tuesday, November 12, 2013 3:09 AM