Is there a time limit for Maintenance Plan execution

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Is there a time limit for Maintenance Plan...

17 posts, Page 1 of 2

1 2 »»

Is there a time limit for Maintenance Plan execution

Rate Topic

Display Mode

Topic Options

Author

Message

SQLSrvrN00b

Posted Thursday, September 27, 2012 12:59 PM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:51 AM
Points: 66, Visits: 454

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

opc.three

Posted Thursday, September 27, 2012 3:09 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 PM
Points: 6,695, Visits: 11,711

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1365521

SQLSrvrN00b

Posted Thursday, September 27, 2012 3:17 PM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:51 AM
Points: 66, Visits: 454

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

opc.three

Posted Thursday, September 27, 2012 3:23 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 PM
Points: 6,695, Visits: 11,711

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1365531

SQLSrvrN00b

Posted Thursday, September 27, 2012 3:34 PM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:51 AM
Points: 66, Visits: 454

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

opc.three

Posted Thursday, September 27, 2012 3:58 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:27 PM
Points: 6,695, Visits: 11,711

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1365549

SQLSrvrN00b

Posted Thursday, September 27, 2012 3:59 PM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:51 AM
Points: 66, Visits: 454

Will do! Thanks again

Post #1365550

lisa.randles

Posted Friday, September 28, 2012 1:48 PM

SSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 2:36 PM
Points: 35, Visits: 337

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

Post #1366099

SQLSrvrN00b

Posted Saturday, September 29, 2012 8:36 AM

Valued Member

Group: General Forum Members
Last Login: 2 days ago @ 10:51 AM
Points: 66, Visits: 454

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

Jeffrey Williams 3188

Posted Saturday, September 29, 2012 10:15 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 11:21 AM
Points: 4,317, Visits: 9,216

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 »

17 posts, Page 1 of 2

1 2 »»

Permissions