none
SQL 2005 database backup appears to hang - process 'suspended' RRS feed

  • Question

  • I am trying to backup a large database (2.3TB) across a network. It does not finish after many hours. The process is in 'suspended' status. Using Hyperbac encrypted compression. Any ideas?
    Wednesday, September 16, 2009 6:50 PM

Answers

  • Bit late now maybe, but you can use the following query to get the progress of the backup (you'll need the SPID of the backup task)

    select session_id,command, percent_complete
    from sys.dm_exec_requests

    HTH

    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Wednesday, September 23, 2009 8:35 AM
  • "Suspended" is a common status for a thread.  It indicates the thread is waiting on an external resource, which backups oftern are.

    Assuming 80% compression, you are still looking at 400-500GB of data to copy across a network link.  That will take some time, especially if the target disks are slow OR the network is not particularly fast.  Even with a fast network, you are looking at several hours.  DOn't give up just yet.

    I would check the target location and see if a file has been started.  The size will be wrong since the file hasn't closed, but if it exists, you know the process is continuing. 


    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP
    Thursday, September 17, 2009 6:13 PM
    Moderator

All replies

  • "Suspended" is a common status for a thread.  It indicates the thread is waiting on an external resource, which backups oftern are.

    Assuming 80% compression, you are still looking at 400-500GB of data to copy across a network link.  That will take some time, especially if the target disks are slow OR the network is not particularly fast.  Even with a fast network, you are looking at several hours.  DOn't give up just yet.

    I would check the target location and see if a file has been started.  The size will be wrong since the file hasn't closed, but if it exists, you know the process is continuing. 


    Geoff N. Hiten Principal Consultant Microsoft SQL Server MVP
    Thursday, September 17, 2009 6:13 PM
    Moderator
  • Bit late now maybe, but you can use the following query to get the progress of the backup (you'll need the SPID of the backup task)

    select session_id,command, percent_complete
    from sys.dm_exec_requests

    HTH

    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link (the green triangle and number on the top-left).

    If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Wednesday, September 23, 2009 8:35 AM