asked
SQL Server Agent Jobs Suspended
We have an intermittent problem on a production instance with SQL Agent jobs. The instance host 40 databases and 30 of these have log shipping enabled. There are nightly backup jobs and numerous log shipping jobs running. Occasionally one of the full backups commences creates a 0 byte file then nothing happens. The job continues to be marked as running but never completes. Worse still all of the other jobs don't start when schedule to do so it's as though the agent has stopped. Stopping and starting the agent usually fixes the problem. On two occasions the SPID that commenced the backup won't go and SQL Server claims to shut down but on restart another instance of SQL Server tries to start ignoring the broken instance. In this scenario only a reboot of the instance fixes things. The instance is running in a VMWare virtual.
This is not something I've specifically run into, so all I can offer is some general advice. First, I would reexamine exactly which script is being used to run the backup processes. Is there anything that could prevent a successful completion? Are you using a third-party backup tool? When your backups are running, can you identify blocking processes? Are there any errors in the error log? How about in the Agent log? Do you have output for the jobs? If not, I'd define one for the job so you can see exactly what the output from the jobs are.
The part of your response 'third-party' backup tool identified the issue. SQL Server ran out space running a backup. The hosting company use Hyperbac to compress the backups from the SQL Server 2005 instance. Although Hyperbac wrote to it's log saying it had ran out of space, and fed back to SQL this fact any subsequent backups stalled. Killing the SPIDs associated with the backups did not help as they went into indefinite rollback. The solution apart from more diskspace was to restart Hyperbac which let the threads fail. Thanks for your help set me of on the right track.
13 People are following this question.
