MS SQL Server - Auto grow

Author

Auto grow

J Pacquiao

2005-06-22, 1:23 pm

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.

Quentin Ran

2005-06-22, 8:23 pm

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@discussion
s.microsoft.com> wrote in message
news:5F4B85B2-1F6D-449C-8846- 1AFE09F9168F@microso
ft.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.

wei xiao [MSFT]

2005-06-24, 3:23 am

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@ya
hoo.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@discussion
s.microsoft.com> wrote in message
> news:5F4B85B2-1F6D-449C-8846- 1AFE09F9168F@microso
ft.com...
am[color=darkred]
I[color=darkred]
>
>

Sponsored Links