SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 2nd File added to Primary Filegroup - Urgent
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dbthj
Posting Yak Master

143 Posts

Posted - 02/16/2009 :  12:31:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/16/2009 :  12:37:49  Show Profile  Reply with Quote
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.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/16/2009 :  12:37:50  Show Profile  Reply with Quote
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.
Go to Top of Page

dbthj
Posting Yak Master

143 Posts

Posted - 02/16/2009 :  12:54:17  Show Profile  Reply with Quote
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?
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/16/2009 :  12:58:57  Show Profile  Reply with Quote
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?

Go to Top of Page

dbthj
Posting Yak Master

143 Posts

Posted - 02/16/2009 :  13:21:41  Show Profile  Reply with Quote
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.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/16/2009 :  14:09:14  Show Profile  Reply with Quote
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.
Go to Top of Page

dbthj
Posting Yak Master

143 Posts

Posted - 02/16/2009 :  14:51:41  Show Profile  Reply with Quote
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.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/16/2009 :  15:03:11  Show Profile  Reply with Quote
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.
Go to Top of Page

dbthj
Posting Yak Master

143 Posts

Posted - 02/16/2009 :  15:26:33  Show Profile  Reply with Quote
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.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 02/16/2009 :  23:45:14  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.15 seconds. Powered By: Snitz Forums 2000