question about secondary file

Hi,
i read about primary and secondary files, but not everything is obvious to
me.
If i define a database with a primary + log file and 3 secondary files:
1) do i have to also define for each secondary file a log file?
2) when i create a table, where is it placed (in the primary, in one of the
secondary files)? Who decides this?
3) same question about data
Thanks
Ben
1) No, you don't need to have a 1:1 relationship between data and log files.
2) If you don't specify a filegroup when you create a table or index, then
it goes into the default filegroup. You can change the default filegroup.
3) See #2. The data go into the table and the table is in whichever
filegroup it was created.
Best practices:
Keep the primary data and log files small. Do not place user objects in the
primary filegroup. Rather, create a separate filegroup and place the
objects there. Change the default filegroup to be something other than the
primary fielgroup.
Note: You can delete secondary data and log files. You cannot delete the
primary ones (file numbers 1 and 2). Thus, to migrate to another file, you
can add files to the user-defined filegroups, empty the original file and
the drop that file. In order to migrate primary files, you need to detach
the DB and move those files. Keeping them small mitigates the downtime.
HTH
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Ben" <b@.en.sd> wrote in message
news:%23QuvGZdfHHA.1816@.TK2MSFTNGP06.phx.gbl...
Hi,
i read about primary and secondary files, but not everything is obvious to
me.
If i define a database with a primary + log file and 3 secondary files:
1) do i have to also define for each secondary file a log file?
2) when i create a table, where is it placed (in the primary, in one of the
secondary files)? Who decides this?
3) same question about data
Thanks
Ben
|||Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> schreef in bericht
news:elNsijdfHHA.4704@.TK2MSFTNGP06.phx.gbl...
> 1) No, you don't need to have a 1:1 relationship between data and log
> files.
> 2) If you don't specify a filegroup when you create a table or index, then
> it goes into the default filegroup. You can change the default filegroup.
> 3) See #2. The data go into the table and the table is in whichever
> filegroup it was created.
> Best practices:
> Keep the primary data and log files small. Do not place user objects in
> the
> primary filegroup. Rather, create a separate filegroup and place the
> objects there. Change the default filegroup to be something other than
> the
> primary fielgroup.
> Note: You can delete secondary data and log files. You cannot delete the
> primary ones (file numbers 1 and 2). Thus, to migrate to another file,
> you
> can add files to the user-defined filegroups, empty the original file and
> the drop that file. In order to migrate primary files, you need to detach
> the DB and move those files. Keeping them small mitigates the downtime.
> HTH
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "Ben" <b@.en.sd> wrote in message
> news:%23QuvGZdfHHA.1816@.TK2MSFTNGP06.phx.gbl...
> Hi,
> i read about primary and secondary files, but not everything is obvious to
> me.
> If i define a database with a primary + log file and 3 secondary files:
> 1) do i have to also define for each secondary file a log file?
> 2) when i create a table, where is it placed (in the primary, in one of
> the
> secondary files)? Who decides this?
> 3) same question about data
> Thanks
> Ben
>