Recently one of my blog readers contacted me and ask if I knew about wait type parallel_backup_queue. I have often got the same question during the Comprehensive Database Performance Health Check. My search on the internet was not giving me a lot of details. Only official documentation which I could find was about wait statistics related to Parallel Backup Queue is very little available online.
Occurs when serializing output produced by RESTORE HEADERONLY, RESTORE FILELISTONLY, or RESTORE LABELONLY.
I asked him, my friend, what exact command he is running, and he informed that he is running below.
RESTORE FILELISTONLY FROM DISK='F:\Backups\ProductionDB.bak'
This is matching with documentation but what is “serializing output”? He informed that he was checking sys.dm_exec_requests DMV and filtering for the SPID which was running this command.
I asked him to check sys.sysprocesses and we found that there were two threads for that SPID. One of them was in a suspended state with wait type parallel_backup_queue and another was running and showing wait of BACKUPIO. I captured process monitor to check IO and I was surprised to see that the above command was reading the whole file in chunks. The database size was close it 1.5 TB.
So, I asked him to leave it running as it was reading the backup file. Finally, we found that it failed with an error:
The media family on device is incorrectly formed.
This made me believe that if there is a problem with the backup media, the commands would take a long time and we might see wait of parallel_backup_queue.
I have not been able to reproduce this in my lab even with 50 GB backup. If you find a way, please share via comments. Or if you have some other findings of Parallel Backup Queue, please share that as well.
Here is the link to the blog post where I discussed SQL Server Wait Stats in detail.
Reference: Pinal Dave (https://blog.sqlauthority.com)
5 Comments. Leave new
I ran across this before. If I am correct, this was a HIPS problem. HIPS was preventing SQL from reading the file. When I moved the file from the location to another location that is when SQL was able to read the file.
I just had this error with a large database (500 GB BAK-file) and it occured after its virtual host unexpectedly shut down. So the BAK-file propably got corrupted. A RESTORE HEADERONLY WITH FILE = ‘xxx’ will get stuck and the SPID will become impossible to KILL…
HI wel,
Did you manage to kill that process somehow?
I just had the same problem while doing RESTORE HEADERONLY from a backup taken from AWS RDS
Killing the Parallel Backup Queue is no easy task… but with that said, I have found only two ways to do it.
1- Stop the SQL Server Service (=BIG Hammer)
2- Attempt to Kill SPID via SSMS Activity Monitor/T-SQL. This will slow it down. Now Run Windows task manager, carefully select the correct SSMS Restore Database process, choose “End Task”
Trying to kill the Parallel Backup Queue process via SSMS or T-SQL alone does not work completely. (shows Killed, but it is still running in background)
Everything in the SSMS GUI may “freeze up” for a few seconds after killing the Parallel Backup Queue process within Task Manager, but it will (should) resume back to normal after that.
Attempt at your own risk if you have other important active SSMS process’ running.