none
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

    Monday, December 10, 2012 4:27 AM

All replies

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

    • Proposed as answer by vr.babu Monday, December 10, 2012 5:43 AM
    Monday, December 10, 2012 5:31 AM
  • Any Other Jobs are scheduled between (21 hours) ,, think about blocking.


    Ramesh Babu Vavilla MCTS,MSBI

    Monday, December 10, 2012 5:44 AM
  • 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

    Monday, December 10, 2012 7:22 AM
  • 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

    Monday, December 10, 2012 2:27 PM
  • 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

    Monday, December 10, 2012 2:38 PM
  • 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

    Monday, December 10, 2012 2:39 PM
  • When I ran sp_who2 'active',I didnt see any blocking.

    A.G

    Monday, December 10, 2012 2:40 PM
  • 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

    Tuesday, December 11, 2012 7:50 AM