Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2002
    Posts
    207

    Unanswered: Backup Database hangs

    SQL Server 2000

    Backup database command hangs. The command I am using is:

    BACKUP DATABASE @DBName TO DISK = @filename WITH INIT

    Size of the database is 140GB and we have 210 GB free disk space.
    sp_who status shows runnable.

    Any help would be appreciated.

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Posts
    492
    How do you execute the command (analyzer?) and what do you mean when you say "Backup database command hangs."? How did you find out it hangs?

  3. #3
    Join Date
    Nov 2002
    Posts
    207
    Command is execeuted thru SQL Server Agent as..

    BACKUP DATABASE [Assentor] TO DISK = 'D:\DB_BAK\AssentorDB2.BAK' WITH INIT , NOUNLOAD , NAME = N'Assentor backup', SKIP , STATS = 10, DESCRIPTION = 'Daily Assentor DB Backup', FORMAT , MEDIANAME = N'Assentor DB Backup', MEDIADESCRIPTION = N'Assentor DB Backup'

    Generally it takes 2 hrs. to complete the backup, but since last few days it just hangs and sits there for hours. I tried to kill the job, it shows killed/rollback and takes hours to release the spid.

    Thanks.

  4. #4
    Join Date
    Nov 2002
    Posts
    207
    Any ideas? Last backup job which kicked of at 12:00 noon is still in runnable state. I can provide more info. if someone needs to help me out.

    Thanks

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Find the spid of the backup process, and see if anything is blocking the backup. If nothing is blocking the backup, see if the CPU and PHYSICAL_IO numbers are going up. That would indicate that the process is running, if slowly. If he numbers are not going up, check the waittype, waitresource, and lastwaittype columns for any resource contention problems.

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Four options (that I know of):

    1. Create multiple filegroups and start backing up on a filegroup level;
    2. Move to a third-party backup solution (SQLLiteSpeed)
    3. Move to SAN
    4. Combination of the three above
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    When the spid appears to hang, substitute the spid for the ?? then run:
    Code:
    SELECT cpu, physical_io FROM master.dbo.sysprocesses WHERE ?? = spid
    WAITFOR DELAY '1:00'
    SELECT cpu, physical_io FROM master.dbo.sysprocesses WHERE ?? = spid
    If the numbers change (at all), then the spid is still running.

    If the numbers don't change, you need to check the disk to see if the backup is growing. Sometimes the spid doing the backup can hang for a long time if the database dump is undergoing a "grow" operation.

    -PatP

  8. #8
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Is it 1 hour or 1 minute?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That should be only one minute, but maybe I goofed. I don't have any easy way to test it at the moment.

    If the counters don't change for a minute, then I'd be willing to consider the task "hung" for most purposes.

    -PatP

  10. #10
    Join Date
    Nov 2002
    Posts
    207
    The whole problem is the backup file is not created at all in this whole process. OR let me put it this way, IF a backup file already exists, ABCbackup.bak with date and time stamp of 10/19/2004 7:30 AM, IT SHOULD CHANGE as soon as I start another backup on 10/20/2004 7:30 AM and the size should start growing. BUT it doesn't happend that way.

    Now that I killed the backup job, it shows KILLED/ROLLBACK state for almost 14 hrs. and is blocking the new backup job which kicked of today morning at 7:30 AM.

    Thanks.

  11. #11
    Join Date
    Nov 2002
    Posts
    207
    The whole problem is the backup file is not created at all in this whole process. OR let me put it this way, IF a backup file already exists, ABCbackup.bak with date and time stamp of 10/19/2004 7:30 AM, IT SHOULD CHANGE as soon as I start another backup on 10/20/2004 7:30 AM and the size should start growing. BUT it doesn't happend that way.

    Now that I killed the backup job, it shows KILLED/ROLLBACK state for almost 14 hrs. and is blocking the new backup job which kicked of today morning at 7:30 AM.

    80 0 runnable ASSENTOR\MSSQL_Srv_Agent NYC-SRA-02 0 Assentor KILLED/ROLLBACK
    80 3 runnable ASSENTOR\MSSQL_Srv_Agent NYC-SRA-02 0 NULL KILLED/ROLLBACK

    Thanks.

  12. #12
    Join Date
    Nov 2002
    Posts
    207
    I opened a case with Microsoft. Will keep you updated on this issue...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •