Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Is there a time limit for Maintenance Plan execution


Is there a time limit for Maintenance Plan execution

Author
Message
SQLSrvrN00b
SQLSrvrN00b
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 596
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



Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8287 Visits: 14368
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
SQLSrvrN00b
SQLSrvrN00b
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 596
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.



Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8287 Visits: 14368
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
SQLSrvrN00b
SQLSrvrN00b
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 596
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.



Orlando Colamatteo
Orlando Colamatteo
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8287 Visits: 14368
These little mysteries tend to bug me Sad

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
SQLSrvrN00b
SQLSrvrN00b
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 596
Will do! Thanks again



lisa.randles
lisa.randles
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 500
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
SQLSrvrN00b
SQLSrvrN00b
Valued Member
Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)Valued Member (67 reputation)

Group: General Forum Members
Points: 67 Visits: 596
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.



Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4468 Visits: 9836
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search