Automate Sliding Window Partition Maintenance: Part III

Question

Automate Sliding Window Partition Maintenance: Part III

Hugh Scott

SSC Eights!

Points: 987
More actions
December 27, 2010 at 10:05 pm

#388511

Comments posted to this topic are about the item Automate Sliding Window Partition Maintenance: Part III

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

I wouldn't have used PoSH for any of this but this is a nice series you've put together. Thanks for taking the time.

Shifting gears a bit, the title of this article is "[font="Arial Black"]Automate [/font]Sliding Window Partitions". You mentioned that you ran the scripts manually for a couple of months but (unless I missed it), you never mentioned how you scheduled these to run automatically each month. What did you use to put it all together on a scheduled basis? Or, is there an up and coming Part IV to this series that will explain how to do that?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Hugh Scott SSC Eights! Points: 987 More actions · Answer 2

It's been a few years, but I seem to recall that I used PoSH because I had trouble getting T-SQL to handle some of the filesystem checks (eg, check for default data file location, check for space). If that sounds vague, it is...I really don't remember.

Ultimately, I did automate the tasks via SQL Agent scheduled task. We would run the script about 3-5 days before the end of the month. That way if it did fail, I'd have a day or two to fix things up before month end rolled around and data would start loading. In the end, we never had an issue with the script failing in production. We only had an issue in non-production (as I recall, the issue would only arise if we refreshed the non-production database after production had run the merge/switch routine).

We recently turned off this solution because we stopped using the database (because we switched to a new product with a new [non-existent] BI solution). The business is only now beginning to realize how much they have lost due to the switch. But that's a story for a different day...

Regards,

Hugh Scott

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

Thanks, Hugh. I appreciate the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)