none
Database Backup hangs and goes in suspended mode cannot kill the process RRS feed

  • Question

  • I am running SQL Server 2005 x64 bit

    The problem I have is B2Bseller Database.
    • I CANNOT RUN A BACKUP TASK or BACKUP USING MAINTENANCE PLAN
    • The backup goes in a never ending cycle
    • I cannot kill the process
    • the process goes under something called "Killed/Rollback"
    • When this happens no other Database backups work
    • I have to stop SQL Server under services to kill this process
    • I CAN run Maintenance options like rebuild and reindex etc...

    I would really appreciate it if anyone can help me with this.

    Thursday, August 12, 2010 3:30 PM

All replies

  • Hi,

    Are you trying to backup database to local drive?. If so, try to backup on network drive or other server.

    Also try to backup using command instead of maintenance plan.


    Ramesh S
    www.sequalserver.com
    Thursday, August 12, 2010 4:52 PM
  • If database size is huge and backup & data files are in same drive, you will face slow performance. Are you configured any other tasks in maintenance plan? like re-indexing, integerity check, history cleanup. If so, please separate them.
    Ramesh S
    www.sequalserver.com
    Thursday, August 12, 2010 4:57 PM
  • Can you run a query against sys.dm_exec_requests and check if the SPID that is executing that backup is getting blocked or waiting for a resource. If this is the case, then the blocking_session_id will have a non-zero value and the wait_time column should also show a non-zero value.

    Killed/Rollback is a state when the cancelled operation is rolling back all the logged operations. Can you also try taking a backup using BACKUP DATABASE T-SQL command and check if the same behavior is exhibited.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.wordpress.com
    Twitter: @banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    Thursday, August 12, 2010 5:18 PM
  • I tried networks thats I have the same results
    Thursday, August 12, 2010 7:43 PM
  • Maintenance works fine .. The backup is going on an E drive ... the size of the DB is 1.5GB only. The backup does not work whether I use Maintenance Plans or directly right click on the database and run Backup.
    Thursday, August 12, 2010 7:47 PM
  • Hi,

    configure performance monitor counters for I/O and disqueue length. If disk queue length is more than 2 and very high, disk has problem.

    http://msdn.microsoft.com/en-us/library/ms175903.aspx


    Ramesh S
    www.sequalserver.com
    Thursday, August 12, 2010 8:12 PM