Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Van
Constraint Violating Yak Guru
462 Posts |
 Posted - 2015-03-11 : 15:22:36
|
| I have a scenario where we have a partitioned table that will eventually need to be spanned onto multiple drives. Is that possible? Right now it's on the E: drive and I will need to have it spanned across the E:, F:, and G: drives. If I create a filegroup on each drive specifically for this table, how do I alter the table to have it span across all the filegroups? I've tried a bit of searching online for this but have had no luck. Thanks in advance.Van |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-11 : 15:33:14
|
| You could use one filegroup with multiple files. You'd then rebuild the table's indexes. Or each index could be in its own filegroup that has however many files you want. Rebuild the indexes in this case too.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2015-03-11 : 15:44:04
|
A simple table resides on a File Group, a partitioned table resides on a set of Partitions. Conceptually, they are the similar. Neither a file group nor a partition has the concept of a drive that it resides on; a file resides on a drive. A file is a member of a file group or a partition. Any file group or partition can have multiple files. These multiple files can reside on different drives. It's not clear to me whether you are aiming to span the partition by placing the file(s) within each partition on separate drives (P1 on D:, P2 on E:, P3 on F:...) or if you want each partition to span multiple drives (P1 on D:,E:,F:, P2 on D:,H:,I:, etc.). Either way is possible.
Those who will not reason, are bigots, those who cannot, are fools, and those who dare not, are slaves. -Lord Byron, poet (1788-1824) |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2015-03-11 : 15:50:03
|
| Thank you Tara. That's exactly what I was trying to think of...multiple files for the filegroup. Have read about it in the past and such but never had to actually implement it. If I add a file (on a different drive) to the filegroup and limit the current file from growing, once the current file hits its limit, the data should start populating the new file on the other drive correct? |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2015-03-11 : 15:54:23
|
| Thanks for the response B. Kool. Here's my exact scenario. We have a purchased app/database and I support it from the database side. Our SAN has a limit of 2TB drive sizes (so each drive is limited to 2TB). This database has a growing partitioned table that will soon exceed 2TB and may grow to 5TB. So I'm just trying to figure out the best way to deal with that. Setting up a couple more 2TB drives and adding files to the filegroup sounds like the best solution. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2015-03-11 : 16:00:49
|
quote:
Originally posted by Van
Thank you Tara. That's exactly what I was trying to think of...multiple files for the filegroup. Have read about it in the past and such but never had to actually implement it. If I add a file (on a different drive) to the filegroup and limit the current file from growing, once the current file hits its limit, the data should start populating the new file on the other drive correct?
Rebuild the index to use the new file. There was a post last week from someone that said it would not use the new file until the rebuild occurred and until then it was bombing out on inserts because the first file was full. I've always rebuilt my indexes after adding new files, so I never encountered the issue.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2015-03-11 : 16:28:58
|
| Thanks again. I have a weekly index rebuild job that should keep me covered on this. |
|
|
|
|
|
|
|
|
Spanning a partitioned table across multiple files