This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(3.2, 54%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EH%3C/text%3E%3C/svg%3E)
hello Folks,
There was an incident happened on weekend. One of our DB backup took 24+ hours to run. usually it runs within 3-4 hours. sp_whoisactive showed me this session was waiting for ASYNC_IO. I checked Avg disk sec/write and read. Both were around .33 for the drive where backup happens. Disk queue length was around 10-12 for this drive. Data and Tran. log drives were ok. Could disk throughput be the issue? We never had this issue before. Our last weekend full backup failed due to our SQL Server got restarted, could this cause the subsequent full backup to take time? I ve seen in the past that when one of our Differential backup failed subsequent differential backup took long time to perfom.
Also after dm_exec_request showed 100% completion, it still took 3 more hours for complete backup operation to be over. During that process sp_whoisactive showed wait for BACKUPIO.
So im bit confused here , after backup operation showed 100% complete what was going on? and is there any way to calculate time it will take to finish post 100% backup operation?
Also, how can i troubleshoot what might have slowed down this backup process, could it be failure of Last week's full db backup or something else.
Hi @Heisenberg
if you have alreay solved the problem pls mark the answer, thank you so much!
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
ASYNC_IO means it is waiting on the target to accept more data, not SQL Server. SQL Server is sending data faster than the recipient can accept the data.
There is almost no information which is helpful about backups. They take as long as they take.
How were you backing up? What type of backup were you doing? How big is the database? What was the target of the backup, tape, disk, SAN? Were you using compression? What is the result of SELECT @@VERSION?
I assume that the wait type you saw was ASYNC_IO_COMPLETION.
This sounds like that there is an issue with the backup device you are writing to, and there is not much you can do from the SQL Server side. Or more exactly, the backup device, or the path to the device. That is, if the device is on the network, the network could be the issue.
Our last weekend full backup failed due to our SQL Server got restarted, could this cause the subsequent full backup to take time?
No, that has nothing to do with it.
Also after dm_exec_request showed 100% completion, it still took 3 more hours for complete backup operation to be over
Once the full backup has completed, BACKUP also needs to backup what's in the transaction log for the duration of the backup. And if the backup is slow, there will be a lot to backup.
Thank you @Erland Sommarskog and @Tom Phillips for the helpful answers. So high possibility its problem with the backup device. But here is additional info. im on SQL 2014, im backing up database using following dynamic sql command
BACKUP DATABASE [' + @DeezNutz + N'] TO DISK = ''' +@fileName + N''' WITH COMPRESSION;'
Ver info: Microsoft SQL Server 2014 (SP3-CU4-GDR) (KB4583462) - 12.0.6433.1 (X64)
Oct 31 2020 02:54:45
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 14393: ) (Hypervisor)
No need for dynamic SQL. This works:
BACKUP DATABASE @name TO DISK = @fileName WITH COMPRESSION;'
Not that it will make your backup run faster, but I still wanted to point it out.
Hi @Heisenberg
II see you check the "Avg disk sec/write and read", did you use the Windows Performance Monitor?
Maybe you can use the code to test the speed of I/O
BACKUP DATABASE AdventureWorks TO DISK = 'NUL' WITH COPY_ONLY
It’s really unnormal that it still needs a lot of time when the backup is 100%.Perhaps you can do the full backup of you SQL but in another disk.
Best regards
Jong
If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"
Thank you all for the answers.. i have one question related to monitoring the drives. Can i capture perfmon counters avg disk sec/read and avg disk sec/write counters using dynamic management views or any sql server query?
Yep,of course you can. If you want to know the whole I/O time, I mean all the backup action that you had done before. You just need to use the following order:
SELECT TOP 100 backup_size, backup_start_date, backup_finish_date
FROM msdb.dbo.backupset
WHERE type = 'D'
ORDER BY backup_set_id
And I think that this article maybe helpful to you https://social.technet.microsoft.com/wiki/contents/articles/3214.sql-server-monitoring-disk-usage.aspx. Pls check this out.