Is there a time limit for Maintenance Plan execution

  • Hi,

    We have a maintenance plan that performs a DBCC against all databases on the server before executing a backup of the databases. The DBCC task is a TSQL task that uses a cursor to loop through all databases and insert into a table info about any failed DBCC Checks. This task takes approximately 3 hours to complete successfully and then moves on to the Backup task. The backup task executes for approximately 14 minutes and does not finish all of the databases but it reports success in the log. This is not unique to this server, it also occurs on other where there are large databases. Does anyone know if there is a time limit for execution of Maintenance Plans?

    Thanks

  • There is no time limit for a Maintenance Plan or for any running batches to execute on the server side. Have you been able to isolate how long it runs before it is stopped? If it is consistent then it could be some external process developed by a misguided soul that goes around looking for long-running batches and kills them.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Thanks for the answer.

    The DBCC Task executes for a little more than 3 hours, then it moves on to the Backup task. It will execute for roughly 7 minutes and stop, report success and move on. The order for which it is backing up is by name, so it is stopping on the master database. This is consistent on all servers that have long running DBCC tasks. This is really strange.

    We have tested another configuration by disabling the DBCC task and ran the Backup task without issues. It performs all backups without problems. This is why we were baffled and thought that there might be some hidden timeout.

    Thanks again.

  • Executing via a SQL Agent job? SQL Agent has had issues allowing called programs to handle their own errors in the past and the response to end the job happens in an inconsistent timeframe after the error initially occurs. I am not saying that's what this is, but the description fits except for the fact that you're saying it happens on every one of your servers, or is it just some?

    Do you capture the warnings and errors coming from the TSQL Task in a file? Anything interesting there? Have you run the DBCC task in an SSMS query window to see that the TSQL runs clean?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • The strange thing is that all of the tasks execute cleanly. There are no errors in the logs. We test all of the code in SSMS before inserting it into the TSQL task in the maintenance plan. We use the maintenance plan as a tidy container.

    Thanks for your help.

  • These little mysteries tend to bug me 🙁

    If you ever nail it down and have a moment to spare please post the root cause.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Will do! Thanks again

  • On a long shot, did you check the job and make sure that the step that backups up the master db doesn't have "On Success Action" set to "Quit the job reporting success" instead of "Go to the next step"...? :hehe:

  • Hi Lisa,

    That is a long shot, but the job completes when run by itself. When the prior task is a long running one, the Backup job only gets through about 7 - 8 minutes of its job and moves to the next task.

    We have now moved the DBCC task to its own job that starts 4 hours before the Backup job. So far we haven't seen the issue repeat.

    Thanks for your input.

  • What is the actual version of each instance where this occurs? It could be a bug in the maintenance plan task - where it only performs the backup task on the master database.

    I saw something similar to this in 2005 - but that was on the integrity check task and that one would loop through each database and perform the integrity check on the master database for each database in the collection.

    It could also be a misconfiguration of the backup task, have you tried recreated the backup task and making sure you have reselected all databases?

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Hi there,

    The backup is performed using an Execute T-SQL task. We wrote the code, tested it in SSMS and it performed without flaws. When the Backup task follows the long running DBCC, this is using an Execute T-SQL Task as well, the backup only executes for about 7-8 minutes and stops reporting success. When we run the code in each of the tasks separately it will complete without problems. The symptom is very strange, no errors, no messages other than success.

    Thanks for your input.

  • Sorry, I forgot the version information. The servers where this is occurring is 2008 SP3 and also on 2008 R2 SP1.

    thanks

  • The next thing this could be is a problem with your script. We need to see the script now to determine if there are issues that would cause a problem.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Jeff,

    The script runs fine outside of the scheduled task when coupled with the DBCC. I find it extremely hard to believe that this is the cause.

    Thanks

  • All the Execute SQL Task does is execute your script - so, if that script is not completing all of the tasks then the script has to be the problem.

    This could be related to how you are looping through the databases. You could be running into an issue where you are trying to access the hidden database snapshot and trying to back it up - which would cause your script to fail.

    You could be trying to access an offline database - which could cause your script to fail. Or, something else...

    If your script is not written to return an error, it would appear to complete successfully 'after 7 minutes' but not actually complete the tasks.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 15 posts - 1 through 15 (of 16 total)

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