two data files in SQL Server

Veeru

Hi

I have a problem with the data migration.
I will explain u the problem

I am using SQL Server 2000 SP4.
My data base is distributed on two drives, lets assume C and D.
The set of datafiles and log files are created on each drive.
C contains
datafile0
logfile0
D contains
datafile1
logfile1.

I am using one tool to migrate the data from one version to another version
on the same machine. During that time the tool sets autogrow enbled. During
migration if one drive filled with data, it has to move to another to write
the remaining data.

But the migration is failed in middle.

I would like to know how the SQL server moves one drive to another if first
drive is not having enough space to write. Is there any concept in SQl Server
like this?

Can you please explain me the multiple data file concepts in this scenario.
How they behave?

Is there any default file group in SQL Server?

If we set the autogrow, is that database level or data file level?
How the datafiles on two drives.

Thanks in advance

Regards
Veeru

TheSQLGuru

1) there is no benefit in this scenario to having 2 log files for the same
database.

2) You should not need to autogrow the file during data load. This will
lead to data fragmentation - probably severe if you use sql server defaults.
make the files as big as they will need to be for the data migration, PLUS
1-2 years of data growth, in the create database statement. Autogrow should
be only for exceptional situations

3) PRIMARY is the 'default' default group. Typically the first file created
with the database.

4) Autogrow is per file.

5) Data is placed in a variety of ways depending on how your table/indexes
are defined. We need more information about this to help.


--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.


"Veeru" <Veeru@discussions.microsoft.com> wrote in message
news:3590B9E6-92BD-4ADC-9889-3F7A89A65541@microsoft.com...
> Hi
>
> I have a problem with the data migration.
> I will explain u the problem
>
> I am using SQL Server 2000 SP4.
> My data base is distributed on two drives, lets assume C and D.
> The set of datafiles and log files are created on each drive.
> C contains
> datafile0
> logfile0
> D contains
> datafile1
> logfile1.
>
> I am using one tool to migrate the data from one version to another
> version
> on the same machine. During that time the tool sets autogrow enbled.
> During
> migration if one drive filled with data, it has to move to another to
> write
> the remaining data.
>
> But the migration is failed in middle.
>
> I would like to know how the SQL server moves one drive to another if
> first
> drive is not having enough space to write. Is there any concept in SQl
> Server
> like this?
>
> Can you please explain me the multiple data file concepts in this
> scenario.
> How they behave?
>
> Is there any default file group in SQL Server?
>
> If we set the autogrow, is that database level or data file level?
> How the datafiles on two drives.
>
> Thanks in advance
>
> Regards
> Veeru

TheSQLGuru

1) there is no benefit in this scenario to having 2 log files for the same
database.

2) You should not need to autogrow the file during data load. This will
lead to data fragmentation - probably severe if you use sql server defaults.
make the files as big as they will need to be for the data migration, PLUS
1-2 years of data growth, in the create database statement. Autogrow should
be only for exceptional situations

3) PRIMARY is the 'default' default group. Typically the first file created
with the database.

4) Autogrow is per file.

5) Data is placed in a variety of ways depending on how your table/indexes
are defined. We need more information about this to help.


--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.


"Veeru" <Veeru@discussions.microsoft.com> wrote in message
news:3590B9E6-92BD-4ADC-9889-3F7A89A65541@microsoft.com...
> Hi
>
> I have a problem with the data migration.
> I will explain u the problem
>
> I am using SQL Server 2000 SP4.
> My data base is distributed on two drives, lets assume C and D.
> The set of datafiles and log files are created on each drive.
> C contains
> datafile0
> logfile0
> D contains
> datafile1
> logfile1.
>
> I am using one tool to migrate the data from one version to another
> version
> on the same machine. During that time the tool sets autogrow enbled.
> During
> migration if one drive filled with data, it has to move to another to
> write
> the remaining data.
>
> But the migration is failed in middle.
>
> I would like to know how the SQL server moves one drive to another if
> first
> drive is not having enough space to write. Is there any concept in SQl
> Server
> like this?
>
> Can you please explain me the multiple data file concepts in this
> scenario.
> How they behave?
>
> Is there any default file group in SQL Server?
>
> If we set the autogrow, is that database level or data file level?
> How the datafiles on two drives.
>
> Thanks in advance
>
> Regards
> Veeru