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.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Spanning a partitioned table across multiple files

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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)
Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

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 Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -