Backup taking longer time than usual

Where is the backup being written to? A disk local to the server? A SAN disk? A file share?

Unless the destination is local, I would be inclined to think that this is a network problem, but that may still require investigation to prove.

If you are on SQL 2016 or later, you can use sys.dm_exec_session_wait_stats to find out what the backup process is waiting for. (But I seem to recall from another post of yours that you are on SQL 2012.)

The delay can also be when reading the data from the data file, not the least if the database is on a SAN. Here sys.dm_io_virtual_filestats can give information about read times.

If you are versed in Performance Monitor, it can also be helpful.

Hi @Jinal Contractor ,

Please firstly check whether the space of the disk is enough for your backup. Or you can backup to another disk to have a test.

Then,if the issue happens again, you trace and analysis the issue as next:

--1.Judging the system bottleneck from the waiting state  
  
select s.session_id,s.status,s.login_time,s.host_name,s.program_name,  
s.host_process_id,s.client_version,s.client_interface_name,s.login_name,s.last_request_start_time,s.last_request_end_time,  
c.connect_time,c.net_transport,c.net_packet_size,c.client_net_address,r.request_id,r.start_time,r.status,r.command,r.database_id,r.user_id,r.blocking_session_id,  
r.wait_type,r.wait_time,r.last_wait_type,r.wait_resource,r.open_transaction_count,r.transaction_id,r.percent_complete,r.cpu_time,r.reads,r.writes,r.granted_query_memory  
from sys.dm_exec_requests r  
right outer join sys.dm_exec_sessions s  
on r.session_id = s.session_id  
right outer join sys.dm_exec_connections c  
on s.session_id =c.session_id   
where s.is_user_process=1  
  
--2.from the result you can check whether there is block or io performance  
  
--3.run below command to enable trace flag, so that we can get more clues about the backup performance in your error log  
DBCC TRACEON(3004, 3051, 3212,3014, 3605, 1816, -1)  
  
--4.after test is done, please turn them off this way:  
DBCC TRACEOFF(3004, 3051, 3212,3014, 3605, 1816, -1)  

More information: options-to-improve-sql-server-backup-performance, how-to-make-sql-server-backups-go-faster

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.

Hi @Jinal Contractor ,

Is the reply helpful?

BR,
Mia

If the answer is helpful, please click "Accept Answer" and upvote it.