Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A native full backup of msdb on a SQL Server 2005 Enterprise instance (9.0.4053) deployed on a two-node Win2K3 cluster (Microsoft Windows NT 5.2 (3790)) is currently stuck in rollback.

The DB is in Simple recovery mode but the Tlog is full. Attempts to add secondary Tlogs are failing due to a 3023 error (regarding the action needing to be serialized).

Options I'm considering are moving non DB files off the volume to allow for autogrow of the Tlog and instance restart. I've tried changing the state of the DB from multi-user to single-user but the SPID stuck in rollback is blocking. SPID 126 is the session in question below.

Are there other options I can consider?

session_id  status  blocking_session_id wait_type   wait_time   wait_resource   transaction_id
1098    suspended   0   TRACEWRITE  687     25828712297
126 suspended   0   BACKUPTHREAD    1284206734      0
146 suspended   0   WAITFOR 500     0
229 suspended   0   TRACEWRITE  250     42236221330
249 suspended   0   TRACEWRITE  531     41659048209
299 suspended   441 LCK_M_U 2149156 DATABASE: 15 [BULKOP_BACKUP_LOG]    0
323 suspended   0   WAITFOR 2656        0
441 suspended   0   BACKUPTHREAD    980511734       0
share|improve this question
    
Even if you restart the instance, it will do a crash recovery and will have to rollback again. what is causing it to hold-up .. run this sql SELECT session_id ,status ,blocking_session_id ,wait_type ,wait_time ,wait_resource ,transaction_id FROM sys.dm_exec_requests WHERE status = N'suspended' –  Kin Oct 4 '13 at 20:57
    
126 suspended 0 BACKUPTHREAD 1284206734 0 –  MattyZDBA Oct 4 '13 at 21:00
    
yes, the wait time translates to 20+ days –  MattyZDBA Oct 4 '13 at 21:01
    
that is not proper output. What is blocking the backupthread ? Is this Production server ? –  Kin Oct 4 '13 at 21:05
    
full output added in question area –  MattyZDBA Oct 4 '13 at 21:16

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.