SQL Server 2008 backup running long and backup threads show suspended

  • SQL Server 2008 r2 - 6 GB memory

    I attempted a backup on a 500GB database but it was taking way too long. I checked the resources on the box and saw the CPU at 100%. I checked the SQL Server activity log and saw a hung query (user was not even logged on) that had multiple threads so I killed it and now the CPU utilization is back to normal.

    Trouble is, now all of the threads in the activity monitor for the backup show 'suspended' and the backup appears to be not doing anything.

    Can anyone help?

  • For how long has been running? If you execute this T-SQL script

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    SELECT

    DB_NAME(er.[database_id]) [DatabaseName]

    ,er.[session_id] AS [SessionID]

    ,er.[command] AS [CommandType]

    ,est.[text] [StatementText]

    ,er.[status] AS [Status]

    ,CONVERT(DECIMAL(5, 2), er.[percent_complete]) AS [Complete_Percent]

    ,CONVERT(DECIMAL(38, 2), er.[total_elapsed_time] / 60000.00) AS [ElapsedTime_m]

    ,CONVERT(DECIMAL(38, 2), er.[estimated_completion_time] / 60000.00) AS [EstimatedCompletionTime_m]

    ,er.[last_wait_type] [LastWait]

    ,er.[wait_resource] [CurrentWait]

    FROM sys.dm_exec_requests AS er

    INNER JOIN sys.dm_exec_sessions AS es ON er.[session_id] = es.[session_id]

    CROSS APPLY sys.dm_exec_sql_text(er.[sql_handle]) est

    WHERE er.[command] = 'BACKUP DATABASE'

    what did you get?

    It may be that is running but just taking a lot of time.

    Also, I don't know how fast your disk's subsystem is and what configuration you may have, but for 500GB we may be talking about 1 to 5hrs, even more.

  • Suspended with what wait type?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • You can get a ballpark guess as to the status of a backup by looking at sys.dm_exec_requests. It shows a percentage complete for backups. Now, it's not terribly accurate, but it can give you a sense of the activity occurring and whether or not your backups are running or are blocked by something. You should also see any blocks there as well.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply