Re: Suggestions for managing large tables

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Mike Hodgson (mwh_junk_at_hotmail.com)
Date: 12/19/04 

Date: Mon, 20 Dec 2004 10:07:42 +1100

> When I get the new logical drive setup I will create another data file
> and link it up to the DB but do i need to setup one for each DB I run as
> they will all run out of space on my Drive E (data drive) and this one 3rd
> party db will be the cause.

Yep, data files are contained within a filegroup and filegroups are
groupings of data files within a specific DB. So each DB you want to
extend to the new LUN, when you get it set up, will need its own
secondary data file on that new LUN.

> If I split the DB byt moving tables/indices to another file in drive <new>
> then should i lock the DB file growth on the first file and allow unlimted
> on the new datafile?

You don't need to "move" anything. Just add a new file to whichever
filegroup (presumedly "PRIMARY") the tables reside on and they'll start
using the space in the new file (data is striped across all files in the
filegroup). If you want to move specific tables and/or indexes to
another disk you'll need to create a 2nd filegroup (called something
other than PRIMARY) containing a data file on the new disk and then move
the table data (ie. clustered index) or indexes as appropriate.

I read a good explanation on this newsgroup a few months ago about how
multiple files within a filegroup fill up. I think it was posted by
Mike Epprecht. And having a 30 second look through SQL BOL I found the
info I'm sure Mike referred to in posting the article:
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_2ak3.asp>

Basically, the files in a filegroup fill up proportionally according to
their free space, so the bottom line is all files within a filegroup
should become 100% full at the same time. Then the files are expanded
round-robin fashion one at a time (which is pretty dumb IMHO as it kinda
defeats the purpose of striping the data within the filegroups). So if
I were you I'd set your file on the disk that's almost full to not
autogrow, then only the new file (on your new LUN) will autogrow when
they're all full.

>
> It seems to work (tried it on lappy) but need some ressurance.
>

Hope this provides some reassurance.

Cheers,
Mike.

Relevant Pages

  • Re: Disk Queue Length counters not meaningful in SQL 2000?
    ... use RAID 10 for everything. ... able to get buy with RAID 5 for data files, RAID 10 for log files, and RAID ... BulkInOut1 - One data File on the E drive. ... Non-Clustered Indexes Created on Primary filegroup - 66 The following tests added the Index filegroup with different file placement. ...
    (microsoft.public.sqlserver.server)
  • Empty data files completely?
    ... Is there a way to completely empty some data files in the same filegroup so ... remove RmEaMkOnViEoTHvIoS from my email ...
    (microsoft.public.sqlserver.server)
  • Add data file to filegroup
    ... Then, right click this new database, select propertied. ... Goto Data Files tab, add a new data file at the second row and select a new ... Set Space Allocated, BUT I cannot set Filegroup to Table, it has ...
    (microsoft.public.sqlserver.server)
  • Filegroups and multi-processor threading improvements
    ... I have read extensively on filegroups vs. RAID 5, ... threads that SQL 2000 will use per filegroup. ... I/O will be limited to one CPU thread. ... Granted disk I/O ...
    (microsoft.public.sqlserver.server)
  • Re: Suggestions for managing large tables
    ... secondary filegroup and (I will read up on it as in truth I have never been ... > logical volume should work fine (if an increase in disk available to ... > of disk space). ... Rebuilding the RAID array to be RAID 5 will use less of ...
    (microsoft.public.sqlserver.server)