Full Database backups are taking long time after upgrading SQL Server 2008 R2 to 2016 (SP2)

Did you change compression settings for your backups?

Hi @Udham Singh ,

You can reference @Shashank Singh ’s suggestion and @tibor_karaszi@hotmail.com ’s suggestion to have a test, install the latest CU or change the compression.

You can also check by next script :

while(1=1)  
begin  
select CURRENT_TIMESTAMP  
select * from sys.sysprocesses where spid>50 and cmd like '%backup%'  
select * from sys.dm_exec_requests where command like  '%backup%'  
if exists(select * from sys.sysprocesses where spid>50 and blocked<>0 and waittime > 10000)    
   begin  
    print '*************blocking detect******************'  
select * from sys.sysprocesses where spid>50  
end  
waitfor delay '00:01:00'  
end  

Troubleshooting steps:

1.Enable trace flag for SQL server.

In management studio, please help run below command to enable trace flag, so that we can get more clues about the backup performance:

DBCC TRACEON(3004, 3051, 3212,3014, 3605, 1816, -1)

After test is done, please turn them off this way:

DBCC TRACEOFF(3004, 3051, 3212,3014, 3605, 1816, -1)

2.Check whether have one IO bottleneck;

More information: slow-backup-or-restore-operations, full-database-backup-takes-so-long?forum=sqldatabaseengine

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hi @Udham Singh ,

Is the reply helpful?

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

OK, so we now know that you are waiting for I/O during the backup operation, considering your wait stats. First, I suggest you read up on this wait stats at: https://www.sqlskills.com/help/waits/async_io_completion/. Make sure you read the full article, especially the "known occurrences" section.

Now enable the trace flags suggested my Mia, and you can after doing a backup study in the errorlog where the time is spent.

You now have some knowledge exactly where you are spending all this time. Perhaps the checkpoint takes a very long time now? Etc. My guess is that something else happened with this upgrade that affected the I/O throughput. But that is for you to find out with the info you'll get from those trace flags.