SQL SERVER – What is Wait Type Parallel Backup Queue?

SQL SERVER - What is Wait Type Parallel Backup Queue? parallel-backup-800x671 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)

, , , ,
Previous Post
SQL SERVER – Different Size of COPY_ONLY Full Backup on Primary and Secondary Replica in Always On
Next Post
SQL SERVER – Microsoft Official Support End Dates for Different Versions

Related Posts

3 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.

    Reply
  • 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…

    Reply
  • HI wel,

    Did you manage to kill that process somehow?

    Reply

Leave a Reply

Menu