question. i have a table that is around 800 million records. i was using partitioning w/ an incrementing identity column to split it across 8 file groups (8 different disks) but far-passed the qty for the last disk, so now all new data is on the last disk. is there any way to design the table/db in a manner so that once all of the disks in the partition are full, it starts back at the beginning of the chain? i basically would like it to evenly distribute the data across all 8 disks and auto grow. probably a really NUB question, and a simple answer, but not sure what to do here.
I'd have one filegroup which would have 8 different files, one file on each drive/mount point. SQL Server uses a proportional fill algorithm to spread the data across them. They don't need to evenly sized like they do for tempdb, but I do keep them at the same size. SQL just tries to the keep the full percentage the same across them, regardless of size.
can you give me a sample db create script for one file group, two files with two separate mount points? i'm playing with the wizard and i'm a little lost, the script would probably spell it out for me.