Any problem for using a single data file?

Hi there,
Is there any problem using 1 data file with restricted file growth
set to 20GB? I've heard that it's better to have multiple data files with
2GB each. Is that true?
Thanks!
AlexThat was true for Win95/98 and FAT file partitions. If you are using
Win2000 or higher and NTFS, the 20 GB single file is just fine.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Alex Cheng" <acheng@.qtcm.com> wrote in message
news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Is there any problem using 1 data file with restricted file growth
> set to 20GB? I've heard that it's better to have multiple data files with
> 2GB each. Is that true?
> Thanks!
> Alex
>|||Unless you're splitting filegroups up in order to put them on different
physical devices, there is, IMO, little benefit in creating multiple data
files. All it will accomplish is creating more of a maintenance headache
for you.
"Alex Cheng" <acheng@.qtcm.com> wrote in message
news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
> Hi there,
> Is there any problem using 1 data file with restricted file growth
> set to 20GB? I've heard that it's better to have multiple data files with
> 2GB each. Is that true?
> Thanks!
> Alex
>|||Hello Alex
It depends on what you are trying to achieve. There is no set requirement
or recommendation either way. However creating a lot of small files for a
database could lead to additional maintenance chores. From performance
standpoint, there should really be no difference either way unless you
achieve stripping with multiple database files across several disk
controllers and drives. However for a database of about 20GB in size, this
striping may only give you small performance benefit.
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks for the information. I'm really appreciated.
alex
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:uBkGIFFkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> That was true for Win95/98 and FAT file partitions. If you are using
> Win2000 or higher and NTFS, the 20 GB single file is just fine.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Alex Cheng" <acheng@.qtcm.com> wrote in message
> news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
growth[vbcol=seagreen]
with[vbcol=seagreen]
>|||Got it. Thanks!
alex
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ej7SNGFkEHA.704@.TK2MSFTNGP09.phx.gbl...
> Unless you're splitting filegroups up in order to put them on different
> physical devices, there is, IMO, little benefit in creating multiple data
> files. All it will accomplish is creating more of a maintenance headache
> for you.
>
> "Alex Cheng" <acheng@.qtcm.com> wrote in message
> news:uQisgrEkEHA.1404@.TK2MSFTNGP09.phx.gbl...
growth[vbcol=seagreen]
with[vbcol=seagreen]
>|||Thanks!
alex
"Pankaj Agarwal [MSFT]" <pankaja@.online.microsoft.com> wrote in message
news:ecnxtZHkEHA.2516@.cpmsftngxa10.phx.gbl...
> Hello Alex
> It depends on what you are trying to achieve. There is no set requirement
> or recommendation either way. However creating a lot of small files for a
> database could lead to additional maintenance chores. From performance
> standpoint, there should really be no difference either way unless you
> achieve stripping with multiple database files across several disk
> controllers and drives. However for a database of about 20GB in size, this
> striping may only give you small performance benefit.
> Thank you for using Microsoft newsgroups.
> Sincerely
> Pankaj Agarwal
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>