June 16, 2017 at 12:31 pm
SQL 2012
What determines the order in which maintenance plan tasks run?
Mine is inconsistent. Several nights the backup will run first, and the cleanup will run last. But every once in a while, like once a week or twice a week, the cleanup task will run first, then the backup runs.
How do I intimidate SQL into always running the tasks in the same order? Is this a bug or is there a patch/fix for this?
Each must both lead and follow. Lead truthfully, and follow with your eyes open.
June 16, 2017 at 3:37 pm
soundchaser59 - Friday, June 16, 2017 12:31 PMSQL 2012
What determines the order in which maintenance plan tasks run?
Mine is inconsistent. Several nights the backup will run first, and the cleanup will run last. But every once in a while, like once a week or twice a week, the cleanup task will run first, then the backup runs.
How do I intimidate SQL into always running the tasks in the same order? Is this a bug or is there a patch/fix for this?
The precedence constraints between the tasks in the package - the flow of these from one task to another determine the order.
If you don't have constraint arrows, you can right click on a task and select Add Precedence Constraint
If you right click on the precedence constraint and then select edit, the precedence constraint editor window will come up which you can use to implement whatever logic, conditions you want to use for the flow of the tasks.
Sue
June 29, 2017 at 11:05 am
Thank You.
The previous dba set them up to run backups first, then remove older backups last. (backups older than 4 days)
But as the data grows daily (retail convenience store sales data) eventually the server's available space is not quite enough for the next backup to run.
If the backup doesn't run, then the entire sequence of tasks fails and none of the older backups get deleted.
Then we simply have no backups running until someone notices and manually deletes the older backups to free up space.
The server admin would have teeth pulled rather than give us more free disk space.
These backups are running about 20GB every night.
Is it always "best practice" to have the backups run first, then delete old files? Is there some compelling reason why we cant set it to delete the oldest file first, then run the new backup? This would allow us to run much longer before the free space gets used up, before we have to beg the admin for more disk allotment.
Each must both lead and follow. Lead truthfully, and follow with your eyes open.
June 29, 2017 at 3:24 pm
soundchaser59 - Thursday, June 29, 2017 11:05 AMThank You.
The previous dba set them up to run backups first, then remove older backups last. (backups older than 4 days)
But as the data grows daily (retail convenience store sales data) eventually the server's available space is not quite enough for the next backup to run.
If the backup doesn't run, then the entire sequence of tasks fails and none of the older backups get deleted.
Then we simply have no backups running until someone notices and manually deletes the older backups to free up space.
The server admin would have teeth pulled rather than give us more free disk space.These backups are running about 20GB every night.
Is it always "best practice" to have the backups run first, then delete old files? Is there some compelling reason why we cant set it to delete the oldest file first, then run the new backup? This would allow us to run much longer before the free space gets used up, before we have to beg the admin for more disk allotment.
It is pretty standard to do the backups first and then the deletes. What if you hit some situation where several of the backups fail and then you hit corruption. And have nothing before the corruption on site. Going off site takes a bit to get and likely won't be within RTOs for a company.
So some of how you handle it depends on how those backups are stored and what you RTO would be. Often you have them first on disk (the ones you want to delete after whatever corporate backup runs), and then on whatever local storage from the corporate backups - quite a few still use tape. And then offsite.
You're kind of weighing out storage vs quickly accessible backups and that's not a good thing. I'd try to get more storage. If that's not going to happen then explain to your boss, the business what the options are, how many you can keep onsite and recovery times from offsite. Anytime you can't get what you need it's a good idea to explain to the business what the options are and the ramifications of each and have them decide. Ultimately those are business decisions anyway. Don't let it look like it's your decision because it really isn't. It's based upon what resources you can get from the business.
Sue
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply