Hi folks
I have 2 questions regarding multiple files in the same filgroup:
1. How to check actual space usage in a file, not to confuse with space in a
database.
2. Does sql distributes data to multiple files in a filegroup proportionaly
to it's size? I remember in BOL for 2k server there is some mentioning about
it. 2005 BOL does not mention that situation.

I like to think that sql will distribute data evenly between files in a file
group but still like to see proof of it.

"Gene." <Gene@discussions.microsoft.com> wrote in message
news:49BF6337-6C04-4AA1-BAFB-50F75804A5E3@microsoft.com...
> Hi folks
> I have 2 questions regarding multiple files in the same filgroup:
> 1. How to check actual space usage in a file, not to confuse with space in
> a
> database.

sp_file returns a usage value!

> 2. Does sql distributes data to multiple files in a filegroup
> proportionaly
> to it's size? I remember in BOL for 2k server there is some mentioning
> about
> it. 2005 BOL does not mention that situation.
>
How about under "Using Files and Filegroups"

"Filegroups use a proportional fill strategy across all the files within
each filegroup. As data is written to the filegroup, the SQL Server Database
Engine writes an amount proportional to the free space in the file to each
file within the filegroup, instead of writing all the data to the first file
until full. It then writes to the next file. For example, if file f1 has 100
MB free and file f2 has 200 MB free, one extent is allocated from file f1,
two extents from file f2, and so on. In this way, both files become full at
about the same time, and simple striping is achieved"


> I like to think that sql will distribute data evenly between files in a
> file
> group but still like to see proof of it.

See above

John

Thank you John, all answered.

"John Bell" wrote:

>
> "Gene." <Gene@discussions.microsoft.com> wrote in message
> news:49BF6337-6C04-4AA1-BAFB-50F75804A5E3@microsoft.com...
> > Hi folks
> > I have 2 questions regarding multiple files in the same filgroup:
> > 1. How to check actual space usage in a file, not to confuse with space in
> > a
> > database.
>
> sp_file returns a usage value!
>
> > 2. Does sql distributes data to multiple files in a filegroup
> > proportionaly
> > to it's size? I remember in BOL for 2k server there is some mentioning
> > about
> > it. 2005 BOL does not mention that situation.
> >
> How about under "Using Files and Filegroups"
>
> "Filegroups use a proportional fill strategy across all the files within
> each filegroup. As data is written to the filegroup, the SQL Server Database
> Engine writes an amount proportional to the free space in the file to each
> file within the filegroup, instead of writing all the data to the first file
> until full. It then writes to the next file. For example, if file f1 has 100
> MB free and file f2 has 200 MB free, one extent is allocated from file f1,
> two extents from file f2, and so on. In this way, both files become full at
> about the same time, and simple striping is achieved"
>
>
> > I like to think that sql will distribute data evenly between files in a
> > file
> > group but still like to see proof of it.
>
> See above
>
> John
>

John, where does sp_file shows actual usage? I only saw size.

"John Bell" wrote:

>
> "Gene." <Gene@discussions.microsoft.com> wrote in message
> news:49BF6337-6C04-4AA1-BAFB-50F75804A5E3@microsoft.com...
> > Hi folks
> > I have 2 questions regarding multiple files in the same filgroup:
> > 1. How to check actual space usage in a file, not to confuse with space in
> > a
> > database.
>
> sp_file returns a usage value!
>
> > 2. Does sql distributes data to multiple files in a filegroup
> > proportionaly
> > to it's size? I remember in BOL for 2k server there is some mentioning
> > about
> > it. 2005 BOL does not mention that situation.
> >
> How about under "Using Files and Filegroups"
>
> "Filegroups use a proportional fill strategy across all the files within
> each filegroup. As data is written to the filegroup, the SQL Server Database
> Engine writes an amount proportional to the free space in the file to each
> file within the filegroup, instead of writing all the data to the first file
> until full. It then writes to the next file. For example, if file f1 has 100
> MB free and file f2 has 200 MB free, one extent is allocated from file f1,
> two extents from file f2, and so on. In this way, both files become full at
> about the same time, and simple striping is achieved"
>
>
> > I like to think that sql will distribute data evenly between files in a
> > file
> > group but still like to see proof of it.
>
> See above
>
> John
>

Did you see my earlier reply to your other post? DBCC SHOWFILESTATS give you space utilization per
data file.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"Gene." <Gene@discussions.microsoft.com> wrote in message
news:50CA909C-1669-4203-B959-08950D96A8AD@microsoft.com...
> John, where does sp_file shows actual usage? I only saw size.
>
> "John Bell" wrote:
>
>>
>> "Gene." <Gene@discussions.microsoft.com> wrote in message
>> news:49BF6337-6C04-4AA1-BAFB-50F75804A5E3@microsoft.com...
>> > Hi folks
>> > I have 2 questions regarding multiple files in the same filgroup:
>> > 1. How to check actual space usage in a file, not to confuse with space in
>> > a
>> > database.
>>
>> sp_file returns a usage value!
>>
>> > 2. Does sql distributes data to multiple files in a filegroup
>> > proportionaly
>> > to it's size? I remember in BOL for 2k server there is some mentioning
>> > about
>> > it. 2005 BOL does not mention that situation.
>> >
>> How about under "Using Files and Filegroups"
>>
>> "Filegroups use a proportional fill strategy across all the files within
>> each filegroup. As data is written to the filegroup, the SQL Server Database
>> Engine writes an amount proportional to the free space in the file to each
>> file within the filegroup, instead of writing all the data to the first file
>> until full. It then writes to the next file. For example, if file f1 has 100
>> MB free and file f2 has 200 MB free, one extent is allocated from file f1,
>> two extents from file f2, and so on. In this way, both files become full at
>> about the same time, and simple striping is achieved"
>>
>>
>> > I like to think that sql will distribute data evenly between files in a
>> > file
>> > group but still like to see proof of it.
>>
>> See above
>>
>> John
>>

If you want a documented way to find the file space usage, you can use the
FILEPROPERTY() function:

select *, fileproperty(name, 'SpaceUsed')*8 as 'SpaceUsed (KB)' from sysfiles

Linchi

"Gene." wrote:

> Hi folks
> I have 2 questions regarding multiple files in the same filgroup:
> 1. How to check actual space usage in a file, not to confuse with space in a
> database.
> 2. Does sql distributes data to multiple files in a filegroup proportionaly
> to it's size? I remember in BOL for 2k server there is some mentioning about
> it. 2005 BOL does not mention that situation.
>
> I like to think that sql will distribute data evenly between files in a file
> group but still like to see proof of it.

"Gene." <Gene@discussions.microsoft.com> wrote in message
news:50CA909C-1669-4203-B959-08950D96A8AD@microsoft.com...
> John, where does sp_file shows actual usage? I only saw size.
>
> "John Bell" wrote:
>

My bad that is that is whether it is data or not! See Tibor's and Linchi's
replies.

John