May 11, 2015 at 10:22 pm
Anything on my questions above, Ken? Also, is the extra disk space you talked about going to be a reality? That would sure simplify things and make this so that there's virtually no down time.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2015 at 10:54 am
Jeff,
We can assume I will have more space on separate LUNs. FYI a few months ago I estimated the need for 2.5TB of space to keep us in good shape for the next year. I got the LUN expanded and then learned our limit is 2TB on windows servers. So the line of business paid for unusable storage. So I intend to get 2 LUNs added at 1.25TB each, relocate the data, then give back the current 2.5TB LUN. That's where I was thinking of DBCC SHRINKFILE EMPTYFILE but if I can accomplish that as part of partitioning then great.
I would like the space saving of switching to VARCHAR but let's not assume that will be happening soon.
If you can say, do steps 1,2,3,etc to partition temporally and move off the current LUN also that would be awesome.
May 12, 2015 at 11:08 am
Ken Davis (5/12/2015)
Jeff,We can assume I will have more space on separate LUNs. FYI a few months ago I estimated the need for 2.5TB of space to keep us in good shape for the next year. I got the LUN expanded and then learned our limit is 2TB on windows servers. So the line of business paid for unusable storage. So I intend to get 2 LUNs added at 1.25TB each, relocate the data, then give back the current 2.5TB LUN. That's where I was thinking of DBCC SHRINKFILE EMPTYFILE but if I can accomplish that as part of partitioning then great.
I would like the space saving of switching to VARCHAR but let's not assume that will be happening soon.
If you can say, do steps 1,2,3,etc to partition temporally and move off the current LUN also that would be awesome.
I'll post the code I used for my 2 hour lecture on the subject and explain it after work tonight. I'll also post my presentation on the subject. I'm writing an article on this subject but the presentation will do for you now.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 12, 2015 at 11:11 am
Sounds great. Thanks again for your time.
May 12, 2015 at 10:48 pm
Please see the attached zip file for what can be done. The Power Point presentation is well worth watching in the presentation mode, especially when you get to the animated slides because they show the problems and the fixes. I strongly recommend watching it before going to the code.
Even though you have the Enterprise Edition, you might want to consider using partitioned views instead of partitioned tables. They have certain advantages that partitioned tables don't and seem to be easier for many folks to understand especially when it comes to the easy removal of the older data. Considering the size of your monthly data, we should probably partition it by week, whichever method you end up wanting to use.
When the code in the Zip file is executed in the obvious order that you'll see, it creates a working example with (IIRC) 4 million rows across 4 months. Pay attention when you run script 030 because you need to run the "usage example" in the header of the proc that gets created to actually build the 4 million row test table.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2015 at 7:20 am
Just to summarize the steps using partitioned tables that I imagine we'd do on your system, Ken, is...
1. Setup a database on the "extra storage".
2. Create the partitioned table with the NULL week , first week, and the next week, as would be done during "normal" operations for a given month.
3. Copy a month and use the normal process to "pack" the partition. This involves the creation of a FileGroup and File for each week to maximize future flexibility for piece-meal restores, rolloffs, etc.
4. Repeat step 3 for each week until we get to the current week.
5. Take the old table offline.
6. Capture any final rows that made it in during the original copy on that final week.
7. Bring the new table online.
8. Setup a job to pack the previous week, (current week will just happen), and setup a new "next week" partition for auto-magic change over when the current week becomes the next week without any unnecessary data movement. This job would also drop off the week(s) older than 6 months, which should be just one week when this system is running on a regular basis.
We'd do similar if we used a partitioned view except it would create individual tables for each week and each table would have it's own indexes. The weekly process would be to build a new next-week table and alter the view to include only the previous six month and then drop the old weekly table.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2015 at 7:38 am
Excellent. I will be reviewing your presentation and instructions. Thanks much. I might be coming back with questions...
May 18, 2015 at 1:06 pm
Jeff,
I will definitely be moving this database to new storage and partitioning as part of the move. I would like to use your process, stored procs, etc. Here are some assumptions/questions regarding your steps. Please comment...
1. Along with the DB I will create 22 Files and 22 File Groups for 5 months of weekly partitions. Or does your step #3 do that for me?
2. I am trying to get my mind around the implications of my two tables' RI relationship. I guess each table gets its own partitioning function and scheme? I am not sure exactly how I create the tables with the "null week, first week, next week" etc. Can you help me understand the "how" of that?
3. Do I just use your "090 Transfer The Data" script to do this?
4. ok
5. ok
6. ok
7. ok
8. Will this job just execute a version of your Audit_MonthlyProcess stored proc that I have modified for my environment?
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply