Backup Jobs Hanging

Question

Backup Jobs Hanging

Eric Heilman

Old Hand

Points: 385
More actions
January 4, 2007 at 5:53 am

#153366

On one of my servers the backup jobs seem to hang about every 30 days or so. Rebooting the server overnight will correct the problem for approximately another 30 days.
What could be causing this to happen? It is not just the full backup that stops, but the transaction log backups as well.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 1

I would imagine that it is being blocked by another process. Possibly an index build or a dbcc command. What else is running when these are being blocked. Have you checked for blocked spids.

Tom

Eric Heilman Old Hand Points: 385 More actions · Answer 2

I found a few blocked spids on the database when the jobs where getting hung. It appears that some indexing may have been causing this problem.

The database that I am having a problem with is in use with a third party software and they may have something running that I do not know about.

I'll check that route too.

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 3

Most of the 3rd party software that I deal with needs sa login for install. During installation they usually create a bunch of backup jobs and maintenance jobs. These are sometimes out there in the jobs queue on sql server. I generally disable or delete them and let my jobs do all maintenance and performance tuning tasks.

Tom

Eric Heilman Old Hand Points: 385 More actions · Answer 4

Upon further investigation, it appears that the job is completing properly and saving my backup file. However, the job will not finish and be marked as complete to allow it to run again at the next scheduled interval.

Any other ideas as to what could be causing this?

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 5

Tom Goltl

SSCertifiable

Points: 5815

January 4, 2007 at 1:37 pm

#681324

Is this a maintenance plan that is running?

Eric Heilman Old Hand Points: 385 More actions · Answer 6

Yes, this job was created from a maintenance plan to back up the transaction log every 3 hours. Could that be part of the problem?

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 7

Are you sure the jobstep is completing? Or are you assuming so because the backup file is there. Could it be hanging doing a verify of the backup?

Mark

MohammedU SSCoach Points: 19644 More actions · Answer 8

Check SQL error log if the backup completing successful you should see the entry of completed backup ..

Did you check MP report and history?

MohammedU
Microsoft SQL Server MVP

Eric Heilman Old Hand Points: 385 More actions · Answer 9

I checked the logs yesterday and it shows that each backup job is completing successfully. I just cannot figure out why the job will not move to complete in the SQL Agent.

Any ideas?

Tom Goltl SSCertifiable Points: 5815 More actions · Answer 10

Is the maintenance plan doing anything else? Reindexing, consistency checking? There are many scripts in the scripting area that seem to do a much better job at managing backups then maintenance plans. I would look at that as a solution.

Tom

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 11

just check in your backup jobs for any schedules that are scheduled to occur monthly and 30th of every month by this you can find what is running concurrently.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com