2nd File added to Primary Filegroup - Urgent

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

General SQL Server Forums

New to SQL Server Programming

2nd File added to Primary Filegroup - Urgent

Author

Topic

dbthj
Posting Yak Master

143 Posts

Posted - 2009-02-16 : 12:31:49

SQL 2000.
A thread from 06/22/2007 : 09:44:07 asked about solving a space
problem. A suggestion was made to add another file to the primary filegroup. But there was a comment from rmiao which said,
"Unless you put second data file on another disk array." This
worries me. Does this approach not work if you locate the new
file on another array? CURRENT PROBLEM: I have some prod databases
on one SAN drive (J:) (one of several). It is nearly out of space. I added another file to the primary filegroup located on another SAN drive (P:). Is this solution going to fail? Will SQL not use the new file even though it is in the same filegroup?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-16 : 12:37:49

Why you need to add new data file in Primary FG? Primary FG stores system metadata as well. You need to create secondary FG and add NDF file to it. Data files are proportionally filled whereas log files are different.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-16 : 12:37:50

dbthj
Posting Yak Master

143 Posts

Posted - 2009-02-16 : 12:54:17

I guess my question was not clear. Clearly, the new file must be added to the Primary filegroup, otherwise the new space will not be used unless objects are manually moved to the new filegroup.

But the comment from rmiao was "Unless you put second data file on another disk array." This seems to say to me, "If you add a second file to the primary filegroup, but locate it on a separate drive, it won't be used".

My example. Primary file was on J: drive. I put second file
(in primary filegroup) on the P: drive. Will the P: drive not be used?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-16 : 12:58:57

quote:
Originally posted by dbthj

I guess my question was not clear. Clearly, the new file must be added to the Primary filegroup, otherwise the new space will not be used unless objects are manually moved to the new filegroup.

Incorrect.You are not getting my point. your files will be proportionally filled.Put in 2nd FG to reduce I/O issue.

But the comment from rmiao was "Unless you put second data file on another disk array." This seems to say to me, "If you add a second file to the primary filegroup, but locate it on a separate drive, it won't be used".

My example. Primary file was on J: drive. I put second file
(in primary filegroup) on the P: drive. Will the P: drive not be used?

It will be used but WHY YOU HAVE TO PUT IN PRIMARY FG WHEN YOU HAVE SEPARATE DISK?

dbthj
Posting Yak Master

143 Posts

Posted - 2009-02-16 : 13:21:41

My tests agree with everything I have read on this subject. That is:
If you add a new filegroup to a database and add a file to it, no data will go to that filegroup - at all - unless you manually move an object (table or index) to that new filegroup. Objects apparently cannot span filegroups. They can, however, span files within the same filegroup.

Tests run

TEST #1.
Create new database with one file in primary filegroup, MAXSIZE 1 MB
Create a table in the database.
Insert data to the table until file fills up.

Now create new filegroup and alter the database to use it. Add a file.

Delete all table data.
Insert data to the table until file fills up.
RESULT: It takes the same amount of data to fill up. New filegroup
is not used.

TEST #2.
Use same database, same table.
Add new file to the Primary filegroup, size 1 mb. MAXSIZE 2 MB.

Insert data to the table until error.
RESULT: file 1 fills up. File 2 grows to 2 MB and error occurs.
file1 fills up, Primary file2 fills up. New filegroup still unused.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-16 : 14:09:14

You can't make primary FG and data offline.Data files fill up proportionally(Best if data files are spread as number of cores of your server).It is case with log files what you are trying to do.

dbthj
Posting Yak Master

143 Posts

Posted - 2009-02-16 : 14:51:41

We have fallen somewhat off-topic, I fear. "data offline.Data" (whatever that is)
does not seem to be part of this discussion. Nor does the number of cores.

I have done some more testing, however and found that, Yes, a second file
added to the primary file group does, in fact, get used . Pages are being allocated
to that file. DBCC SHOWFILESTATS shows this to be the case.

Now, for the second (and final, I hope) question: Now that I have two files in
my Primary file group (one quite full and one mostly unused) how can I get the
data to be evenly distributed between them? SQL Server doesn't have a reorg utility
that I know of.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-16 : 15:03:11

thats why i said having data files spread according to number of cores will fill up proportionally (evenly).I think I have specified this number of times.

dbthj
Posting Yak Master

143 Posts

Posted - 2009-02-16 : 15:26:33

Yes you did. But I'm afraid I haven't been able to translate the number of cores into anything meaningful about files and filegroups for the current problem. For now let's assume we have one filegroup (primary) with two files on two separate drives. My last question was about whether we can distribute the data evenly between the two files. My question is, "can we do that? And if so, how?".

Ultimately, it might be nice to have one filegroup for each core
and have the database objects distributed among them. But that's a big
project. It would take a long time to implement that sort of thing
into the production environment. I need to do something quickly
to relieve a disk problem and keep the database available.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-16 : 23:45:14

There could lot of reasons why data files are not proportionally-filled like Autogrow option(Which distracts that rule)and spreading across number of cores. It fills in round-robin fashion.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources