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 Sunday, September 30, 2012 12:22 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 67, Visits: 529
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.



Post #1366285
Posted Sunday, September 30, 2012 12:29 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 67, Visits: 529
Sorry, I forgot the version information. The servers where this is occurring is 2008 SP3 and also on 2008 R2 SP1.

thanks



Post #1366287
Posted Monday, October 1, 2012 8:18 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:31 AM
Points: 4,358, Visits: 9,537
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1366593
Posted Monday, October 1, 2012 8:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 67, Visits: 529
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



Post #1366603
Posted Monday, October 1, 2012 8:51 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:31 AM
Points: 4,358, Visits: 9,537
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
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1366605
Posted Monday, October 1, 2012 9:16 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 8:40 AM
Points: 67, Visits: 529
The server in question does not have any snapshots or hidden DBs. The DBCC task that runs prior to this looks for databases that are offline or unavailable. If this task finds a db in an unstable state, it is logged in a table and that database is skipped during the backup portion.

We are changing this to run by database_id and will let it run its course this evening.

Thanks



Post #1366618
Posted Monday, October 1, 2012 9:22 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:31 AM
Points: 4,358, Visits: 9,537
SQLSrvrN00b (10/1/2012)
The server in question does not have any snapshots or hidden DBs. The DBCC task that runs prior to this looks for databases that are offline or unavailable. If this task finds a db in an unstable state, it is logged in a table and that database is skipped during the backup portion.

We are changing this to run by database_id and will let it run its course this evening.

Thanks


The integrity check (DBCC CHECKDB) creates a temporary hidden database snapshot to perform the integrity check. If that is not getting removed immediately following the integrity check task - it could possibly be available to your script and you could end up trying to back it up.

Issues like this - generally - end up being related to the code that is being run. If you were running the built-in tasks and this was happening, it would be a bug in those tasks and could be fixed with a service pack or update. Since this is code you have written - it will need to be corrected on your side.

If you are not returning an error for a particular condition - that would make your script (and the task) appear to be successful without actually performing the work.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

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

Add to briefcase ««12

Permissions Expand / Collapse