Full backup runs very slow(It used to be fast)

Question

We have  database(4.6TB) in SQL SERVER 2008 R2.On weekly bases we take full backup from this database.It used to take 7-8 hours for the full backup.but now It is running for 21 hours and still it is not completed.How can I check what is the problem?

Thanks

---

A.G

Answer 1

Check is it is waiting on something? query sysprocesses and sys.dm_os_wait_stats for the BACKUP spid

---

This posting is provided “AS IS” with no warranties, and confers no rights.

If this reply answers your question, please mark it as Answered for others to find it easily.
If this reply help you resolving the problem, please vote the post as Helpful.

Answer 2

Any Other Jobs are scheduled between (21 hours) ,, think about blocking.

---

Ramesh Babu Vavilla MCTS,MSBI

Answer 3

What does below command return?

select top 2 start_time,
 percent_complete ,estimated_completion_time 
 from sys.dm_exec_requests 
order by start_time desc

It is possible that another process 'locks' the database (could be a third party tool like NetBackup performs backup at the same time on the same database)

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 4

When I ran the query,I got the following result:

start_time              percent_complete estimated_completion_time

----------------------- ---------------- -------------------------

2012-12-10 14:24:09.313 0                0

2012-12-10 14:24:08.630 0                0

also when I ran the SP_who2 'active',It showed 3 processes Backup database.All of them have the same SPID and have the same starttime.But two of them of them they dont have any login name.

---

A.G

Answer 5

Ok, your backup have not started yet, it is blocked by another process ,(you have to find one)

BTW is there a lot of snow in Mourmansk :-)

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 6

Here is wait result:

in sysprocesses

LastWaitType

ASYNC_IO_COMPLETION            
BACKUPBUFFER                   
BACKUPIO                       

in sys.dm_os_wait_stats

wait_type                                                    wait_time_s                             pct                                     running_pct

------------------------------------------------------------ --------------------------------------- --------------------------------------- ---------------------------------------

PAGEIOLATCH_SH                                         1922222.73                              22.77                                   22.77

DISPATCHER_QUEUE_SEMAPHORE                  744597.78                               8.82                                    31.59

WRITELOG                                                     579965.42                               6.87                                    38.46

PAGEIOLATCH_EX                                          534665.43                               6.33                                    44.79

LCK_M_SCH_M                                              505318.80                               5.99                                    50.78

CXPACKET                                                     429234.53                               5.08                                    55.86

XE_TIMER_EVENT                                          400411.63                               4.74                                    60.61

XE_DISPATCHER_WAIT                                  400371.06                               4.74                                    65.35

REQUEST_FOR_DEADLOCK_SEARCH               399692.42                               4.73                                    70.08

FT_IFTS_SCHEDULER_IDLE_WAIT                   397147.65                               4.70                                    74.79

LOGMGR_QUEUE                                            394232.98                               4.67                                    79.46

CHECKPOINT_QUEUE                                      340782.02                               4.04                                    83.49

PAGEIOLATCH_UP                                          289433.42                               3.43                                    86.92

BACKUPBUFFER                                              201189.41                               2.38                                    89.31

BROKER_TO_FLUSH                                        200214.07                               2.37                                    91.68

SOS_SCHEDULER_YIELD                                 148981.91                               1.76                                    93.44

ASYNC_IO_COMPLETION                                 137984.34                               1.63                                    95.08

---

A.G

Answer 7

When I ran sp_who2 'active',I didnt see any blocking.

---

A.G

Answer 8

Hi Mourmansk,

Please check whether there is job which uses differential backup or transaction log backup on the database at the same time. Additionally, please run “sp_who2” command again, and get the following column value of the backup session:

CPUTime, DiskIO, ProgramName

You can also try to back up database to another disk.

---

Allen Li
TechNet Community Support