0

I am facing a strange issue with daily FULL backup. We have a total of 23 databases. We have 1.21 TB big database in this SQL server 2012 Standard Edition. We are taking daily FULL database backup for all databases.

For the last 2 days the backup is taking too long. After more than 10:30 hours it had only completed 67 % of the backup. Daily Full backup job completion time is normally 4 to 5 hours.

And the backup job is also showing suspended state. I found last_wait_type is ASYNC_IO_COMPLETION for this backup job.

We have sufficient disk space available.

Can anyone make any suggestions on this issue?

  • Check the I/O performance on the disk where the backup files are begin written. Ask your system admin / storage admin if something changed or if they notice any storage issues. – Tony Hinkle Aug 8 at 12:29
  • I have an small question in this, i am getting successful email alert with in 3 Sec. But backup is still running can you know why is this happening ? – HARI VARMA Aug 9 at 6:53
1

I found last_wait_type is ASYNC_IO_COMPLETION for this backup job. We have sufficient disk space available.

This blogpost from Paul Randal gives you most of the information regarding this wait stat

...The official definition of ASYNC_IO_COMPLETION is ‘Occurs when a task is waiting for I/Os to finish....

...Long-duration ASYNC_IO_COMPLETION waits commonly are from data backups....

A question similar to yours can be found on that same blog. In his case a backup of a small database (1,5GB) was taking over 15 minutes.

Q

...I’m facing a high level of ASYNC_IO_COMPLETION into one of my environments what is resulting in backups of small databases (around 1,5GB) to take more than 15 minutes. I have a feeling that it is related to low performance on the I/O subsystem. But I’m not 100% confidente to go ahead on this diagnostic to ask for analysis or changes on that.

A

Yup – that’s what it is. Try doing a backup to nul, as that will give you time taken to read from the data files. The difference is the time being taken to write out the backup files – there’s your ammo.

This is the command to do that:

BACKUP DATABASE DatabaseName TO DISK = 'NUL'

And compare this with the backup time you mentioned previously.

Keep in mind that this will still start a huge amount of I/O operations on your server.

And the backup job is also showing suspended state

Suspended state simply means that it is waiting on resources, I/O based in your case.


Other improvements

Check what else is running on the instance, any maintenance plans such as index rebuilds, statistic updates, integrity checks?

You could start logging queries with sp_whoisactive and perhaps logging it in a table.

Striping your backups accross multiple files can help in reducing backup time. More on that here. My go to is 4 files but you could test different settings.

Since you are on sql server 2012, if you are not using TDE you could leverage backup compression as well.

More information on making backups go faster can be found here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.