Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Is there a time limit for Maintenance Plan execution Expand / Collapse
Author
Message
Posted Thursday, September 27, 2012 12:59 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:45 AM
Points: 67, Visits: 524
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



Post #1365461
Posted Thursday, September 27, 2012 3:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 4:51 PM
Points: 7,097, Visits: 12,597
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
Post #1365521
Posted Thursday, September 27, 2012 3:17 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:45 AM
Points: 67, Visits: 524
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.



Post #1365526
Posted Thursday, September 27, 2012 3:23 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 4:51 PM
Points: 7,097, Visits: 12,597
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
Post #1365531
Posted Thursday, September 27, 2012 3:34 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:45 AM
Points: 67, Visits: 524
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.



Post #1365539
Posted Thursday, September 27, 2012 3:58 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 4:51 PM
Points: 7,097, Visits: 12,597
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
Post #1365549
Posted Thursday, September 27, 2012 3:59 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:45 AM
Points: 67, Visits: 524
Will do! Thanks again


Post #1365550
Posted Friday, September 28, 2012 1:48 PM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 7:06 AM
Points: 52, Visits: 448
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"...?
Post #1366099
Posted Saturday, September 29, 2012 8:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, July 28, 2014 7:45 AM
Points: 67, Visits: 524
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.



Post #1366192
Posted Saturday, September 29, 2012 10:15 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:37 PM
Points: 4,389, Visits: 9,522
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1366203
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse