Sql-server – SQL Full Backup job is taking too long to complete

backupsql serversql-server-2012

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?

Best Answer

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.

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

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

Related Solutions

Sql-server – Scheduled backup task doesn’t always backup all databases despite always saying job successful

I would add TRY/CATCH blocks to handle errors and log them. The DB could be in single user, being restored or whatever.

Without this, errors can abort in a way that no errors are logged (statement, batch, scope, connection etc)

With TRY/CATCH then everything except for compile or connection aborting errors are logged? but I doubt this is the case.

I'd also use sys.databases which replaces sysdatabases and read more flags:

-- declares etc

BEGIN TRY

    DECLARE db_cursor CURSOR FOR  
    SELECT name, state, user_access
    FROM sys.databases 
    WHERE name NOT IN ('tempdb')

    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @name, @state, @user_access

    WHILE @@FETCH_STATUS = 0   
    BEGIN   

        SET @fileName = @path + @name + '.bak'
        IF @state = 0 AND user_access = 0
        BEGIN
            BEGIN TRY
                BACKUP DATABASE @name TO DISK = @fileName  WITH INIT
            END TRY
            BEGIN CATCH
                -- log but do not rethrow so loop continues
            END CATCH
            WAITFOR DELAY '00:00:20'
        END
        ELSE
           --log user and/or state issues

        FETCH NEXT FROM db_cursor INTO @name 
    END   

    CLOSE db_cursor   
    DEALLOCATE db_cursor

END TRY
BEGIN CATCH
  -- some useful stuff here
END CATCH

Sql-server – SQL Server 2012 defrag job runs interactively, but not as a job – why

Ok, I found a resolution by doing the following:

I moved dba_indexDefrag_sp and its associated objects into a 'HouseKeeper' database, rather than putting them in the Master database. (I note Michelle Ufford recommends this, saying: 'It’s up to you where you create it. You could technically create it in the MASTER database, but I recommend creating a utility database for your DBA administrative tasks.')
I created a new 'SQLJobRunner' Windows login, then added it to SQL Server, setting it as db_owner on the dbs I'm defragmenting. I also applied to same to the HouseKeeper db, to give the login full access to that database.
I changed the defrag SQL Agent job to run using the 'SQLJobRunner' login.

and then it worked, which deals with my issue. It is notable that I have the same security setup against my own login and that still doesn't work. Odd... but that isn't really a problem for me now - I don't know why it that doesn't work though.

Note that I'm aware that giving the login db_owner is a bit of a sledgehammer approach, but now that it works I'm able to make the security a bit more fine-grained.

Thanks!

Best Answer

Related Solutions

Sql-server – Scheduled backup task doesn’t always backup all databases despite always saying job successful

Sql-server – SQL Server 2012 defrag job runs interactively, but not as a job – why

Related Question