AutoGrowth - Which data file does SQL Prefers to auto grow & why ?

Win2k3, SQL 2000, SP4
If we have multiple datafiles in a filegroup, lets say 8,
How does SQL distribute data ?(Will all data for a single table will go in
one file or will it be spread)
When there is need for space are all files auto grown uniformely OR just one
file ?
If just one file, will the same file auto grow next time too ?
Sorry to put too many questions ? Just trying to understand the Auto grow
feature of SQL.
Advance thanks for sharing the knowledge...
RangaHi
Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_9sab.asp
and
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_2ak3.asp
Filegroups use a proportional fill strategy across all the files within each
filegroup. If there are multiple files in a filegroup, they do not autogrow
until all the files are full. The file to grow is then chosen using a
round-robin algorithm.
John
"Ranga" wrote:
> Win2k3, SQL 2000, SP4
> If we have multiple datafiles in a filegroup, lets say 8,
> How does SQL distribute data ?(Will all data for a single table will go in
> one file or will it be spread)
> When there is need for space are all files auto grown uniformely OR just one
> file ?
> If just one file, will the same file auto grow next time too ?
> Sorry to put too many questions ? Just trying to understand the Auto grow
> feature of SQL.
> Advance thanks for sharing the knowledge...
> Ranga

AutoGrowth - Which data file does SQL Prefers to auto grow & why ?

Win2k3, SQL 2000, SP4
If we have multiple datafiles in a filegroup, lets say 8,
How does SQL distribute data ?(Will all data for a single table will go in
one file or will it be spread)
When there is need for space are all files auto grown uniformely OR just one
file ?
If just one file, will the same file auto grow next time too ?
Sorry to put too many questions ? Just trying to understand the Auto grow
feature of SQL.
Advance thanks for sharing the knowledge...
Ranga
Hi
Check out
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_9sab.asp
and
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_2ak3.asp
Filegroups use a proportional fill strategy across all the files within each
filegroup. If there are multiple files in a filegroup, they do not autogrow
until all the files are full. The file to grow is then chosen using a
round-robin algorithm.
John
"Ranga" wrote:

> Win2k3, SQL 2000, SP4
> If we have multiple datafiles in a filegroup, lets say 8,
> How does SQL distribute data ?(Will all data for a single table will go in
> one file or will it be spread)
> When there is need for space are all files auto grown uniformely OR just one
> file ?
> If just one file, will the same file auto grow next time too ?
> Sorry to put too many questions ? Just trying to understand the Auto grow
> feature of SQL.
> Advance thanks for sharing the knowledge...
> Ranga

AutoGrowth - Which data file does SQL Prefers to auto grow & why ?

Win2k3, SQL 2000, SP4
If we have multiple datafiles in a filegroup, lets say 8,
How does SQL distribute data ?(Will all data for a single table will go in
one file or will it be spread)
When there is need for space are all files auto grown uniformely OR just one
file ?
If just one file, will the same file auto grow next time too ?
Sorry to put too many questions ? Just trying to understand the Auto grow
feature of SQL.
Advance thanks for sharing the knowledge...
RangaHi
Check out
http://msdn.microsoft.com/library/d...r />
_9sab.asp
and
http://msdn.microsoft.com/library/d...>
_02_2ak3.asp
Filegroups use a proportional fill strategy across all the files within each
filegroup. If there are multiple files in a filegroup, they do not autogrow
until all the files are full. The file to grow is then chosen using a
round-robin algorithm.
John
"Ranga" wrote:

> Win2k3, SQL 2000, SP4
> If we have multiple datafiles in a filegroup, lets say 8,
> How does SQL distribute data ?(Will all data for a single table will go in
> one file or will it be spread)
> When there is need for space are all files auto grown uniformely OR just o
ne
> file ?
> If just one file, will the same file auto grow next time too ?
> Sorry to put too many questions ? Just trying to understand the Auto grow
> feature of SQL.
> Advance thanks for sharing the knowledge...
> Ranga

Auto grow

I have two files set up for one filegroup and the primary file was set to NO
AUTO GROW since the drive it was pointing to does not have enough space
available. The secondary file was set to AUTO GROW and the drive space has
enough available space. Provided that the secondary file has plenty of room
for new insert will there be any case that the new INSERT will fail? I am
trying to troubleshoot an issue on which the INSERT was failing but when I
was reset the primary file to AUTO GROW the problem disappeared.
Yes SQL server will write proportionally to the files according to their
allocated sizes. You will have to move the file that can no longer grow to
a drive where it has space to grow. Or you may put the content of the file
that can no longer grow into the other file(s).
hth
Quentin
"J Pacquiao" <JPacquiao@.discussions.microsoft.com> wrote in message
news:5F4B85B2-1F6D-449C-8846-1AFE09F9168F@.microsoft.com...
>I have two files set up for one filegroup and the primary file was set to
>NO
> AUTO GROW since the drive it was pointing to does not have enough space
> available. The secondary file was set to AUTO GROW and the drive space
> has
> enough available space. Provided that the secondary file has plenty of
> room
> for new insert will there be any case that the new INSERT will fail? I am
> trying to troubleshoot an issue on which the INSERT was failing but when I
> was reset the primary file to AUTO GROW the problem disappeared.
|||If there are two files in a database, and one file is full without the AUTO
GROW setting, then SQL Server should only try to allocate from the other
file. So most likely the insert will not fail as long as the other file can
auto grow or has free space.
From a performance point of view, this is not the ideal situation for your
database. We recommend that the database files are configured so that each
file has some free space in it.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:#um$101dFHA.2960@.TK2MSFTNGP10.phx.gbl...
> Yes SQL server will write proportionally to the files according to their
> allocated sizes. You will have to move the file that can no longer grow
to
> a drive where it has space to grow. Or you may put the content of the
file[vbcol=seagreen]
> that can no longer grow into the other file(s).
> hth
> Quentin
> "J Pacquiao" <JPacquiao@.discussions.microsoft.com> wrote in message
> news:5F4B85B2-1F6D-449C-8846-1AFE09F9168F@.microsoft.com...
am[vbcol=seagreen]
I
>

Auto grow

I have two files set up for one filegroup and the primary file was set to NO
AUTO GROW since the drive it was pointing to does not have enough space
available. The secondary file was set to AUTO GROW and the drive space has
enough available space. Provided that the secondary file has plenty of room
for new insert will there be any case that the new INSERT will fail? I am
trying to troubleshoot an issue on which the INSERT was failing but when I
was reset the primary file to AUTO GROW the problem disappeared.Yes SQL server will write proportionally to the files according to their
allocated sizes. You will have to move the file that can no longer grow to
a drive where it has space to grow. Or you may put the content of the file
that can no longer grow into the other file(s).
hth
Quentin
"J Pacquiao" <JPacquiao@.discussions.microsoft.com> wrote in message
news:5F4B85B2-1F6D-449C-8846-1AFE09F9168F@.microsoft.com...
>I have two files set up for one filegroup and the primary file was set to
>NO
> AUTO GROW since the drive it was pointing to does not have enough space
> available. The secondary file was set to AUTO GROW and the drive space
> has
> enough available space. Provided that the secondary file has plenty of
> room
> for new insert will there be any case that the new INSERT will fail? I am
> trying to troubleshoot an issue on which the INSERT was failing but when I
> was reset the primary file to AUTO GROW the problem disappeared.|||If there are two files in a database, and one file is full without the AUTO
GROW setting, then SQL Server should only try to allocate from the other
file. So most likely the insert will not fail as long as the other file can
auto grow or has free space.
From a performance point of view, this is not the ideal situation for your
database. We recommend that the database files are configured so that each
file has some free space in it.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:#um$101dFHA.2960@.TK2MSFTNGP10.phx.gbl...
> Yes SQL server will write proportionally to the files according to their
> allocated sizes. You will have to move the file that can no longer grow
to
> a drive where it has space to grow. Or you may put the content of the
file
> that can no longer grow into the other file(s).
> hth
> Quentin
> "J Pacquiao" <JPacquiao@.discussions.microsoft.com> wrote in message
> news:5F4B85B2-1F6D-449C-8846-1AFE09F9168F@.microsoft.com...
am[vbcol=seagreen]
I[vbcol=seagreen]
>

Auto grow

I have two files set up for one filegroup and the primary file was set to NO
AUTO GROW since the drive it was pointing to does not have enough space
available. The secondary file was set to AUTO GROW and the drive space has
enough available space. Provided that the secondary file has plenty of room
for new insert will there be any case that the new INSERT will fail? I am
trying to troubleshoot an issue on which the INSERT was failing but when I
was reset the primary file to AUTO GROW the problem disappeared.Yes SQL server will write proportionally to the files according to their
allocated sizes. You will have to move the file that can no longer grow to
a drive where it has space to grow. Or you may put the content of the file
that can no longer grow into the other file(s).
hth
Quentin
"J Pacquiao" <JPacquiao@.discussions.microsoft.com> wrote in message
news:5F4B85B2-1F6D-449C-8846-1AFE09F9168F@.microsoft.com...
>I have two files set up for one filegroup and the primary file was set to
>NO
> AUTO GROW since the drive it was pointing to does not have enough space
> available. The secondary file was set to AUTO GROW and the drive space
> has
> enough available space. Provided that the secondary file has plenty of
> room
> for new insert will there be any case that the new INSERT will fail? I am
> trying to troubleshoot an issue on which the INSERT was failing but when I
> was reset the primary file to AUTO GROW the problem disappeared.|||If there are two files in a database, and one file is full without the AUTO
GROW setting, then SQL Server should only try to allocate from the other
file. So most likely the insert will not fail as long as the other file can
auto grow or has free space.
From a performance point of view, this is not the ideal situation for your
database. We recommend that the database files are configured so that each
file has some free space in it.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://blogs.msdn.com/weix
"Quentin Ran" <remove_this_qran2@.yahoo.com> wrote in message
news:#um$101dFHA.2960@.TK2MSFTNGP10.phx.gbl...
> Yes SQL server will write proportionally to the files according to their
> allocated sizes. You will have to move the file that can no longer grow
to
> a drive where it has space to grow. Or you may put the content of the
file
> that can no longer grow into the other file(s).
> hth
> Quentin
> "J Pacquiao" <JPacquiao@.discussions.microsoft.com> wrote in message
> news:5F4B85B2-1F6D-449C-8846-1AFE09F9168F@.microsoft.com...
> >I have two files set up for one filegroup and the primary file was set to
> >NO
> > AUTO GROW since the drive it was pointing to does not have enough space
> > available. The secondary file was set to AUTO GROW and the drive space
> > has
> > enough available space. Provided that the secondary file has plenty of
> > room
> > for new insert will there be any case that the new INSERT will fail? I
am
> > trying to troubleshoot an issue on which the INSERT was failing but when
I
> > was reset the primary file to AUTO GROW the problem disappeared.
>