database size

Hi all, I have an asp application that allows users to upload files and
these are being stored in a sql database. The database size is growing
relatively fast (at about 1 GB) now. I know the recommended way of storing
uploaded files is on the file system but I chose the database for several
reasons including security and easy of backup since all data is in 1 central
location. My question is does the size of the database effect the overall
performance of the sql server as far as that db is concerned? I have many
other tables in that database being used in various other operations daily.
Would there be any benefit in maybe moving the uploaded file tables to a
different database?
much thanks in advance!Of course you could have some impact on performance. You regular tables
could become fragmented. But you could still have the uploaded files in the
database, just put them to a separate data file (a database can have many
data files). If the file would be on a separate disk, this would be even
better. Otherwise make sure that the primary data file (with regular tables)
is big enough, so it will not expand, otherwise you could get disk
fragmentation.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"RP" <rp@.nospam.com> wrote in message
news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> Hi all, I have an asp application that allows users to upload files and
> these are being stored in a sql database. The database size is growing
> relatively fast (at about 1 GB) now. I know the recommended way of storing
> uploaded files is on the file system but I chose the database for several
> reasons including security and easy of backup since all data is in 1
central
> location. My question is does the size of the database effect the overall
> performance of the sql server as far as that db is concerned? I have many
> other tables in that database being used in various other operations
daily.
> Would there be any benefit in maybe moving the uploaded file tables to a
> different database?
> much thanks in advance!
>|||Dejan, thank you for your reply. I like the idea of multiple data files for
a database. How would I go about setting this up? Can I specify certain
tables to certain data files? Right now the database has 1 data and 1 log
file and each one is set to grow automatically by 10%. Would multiple data
files affect my backup settings? I have the server setup to backup the
database on a daily basis. Would it backup each data file or just one?
Your help is much appreciated.
thanks a lot!
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:OZzhCTGJEHA.628@.TK2MSFTNGP11.phx.gbl...
> Of course you could have some impact on performance. You regular tables
> could become fragmented. But you could still have the uploaded files in
the
> database, just put them to a separate data file (a database can have many
> data files). If the file would be on a separate disk, this would be even
> better. Otherwise make sure that the primary data file (with regular
tables)
> is big enough, so it will not expand, otherwise you could get disk
> fragmentation.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "RP" <rp@.nospam.com> wrote in message
> news:%23dc0xH7IEHA.3964@.TK2MSFTNGP10.phx.gbl...
> > Hi all, I have an asp application that allows users to upload files and
> > these are being stored in a sql database. The database size is growing
> > relatively fast (at about 1 GB) now. I know the recommended way of
storing
> > uploaded files is on the file system but I chose the database for
several
> > reasons including security and easy of backup since all data is in 1
> central
> > location. My question is does the size of the database effect the
overall
> > performance of the sql server as far as that db is concerned? I have
many
> > other tables in that database being used in various other operations
> daily.
> > Would there be any benefit in maybe moving the uploaded file tables to a
> > different database?
> >
> > much thanks in advance!
> >
> >
>|||>
> I like the idea of multiple data files for
> a database. How would I go about setting this up?
--
You can use the ALTER DATABASE with the ADD FILEGROUP option.
> Can I specify certain tables to certain data files?
--
You can use the CREATE TABLE with ON <filegroup> clause to specify which
filegroup the table will be stored.
> Would multiple data files affect my backup settings?
--
You can backup the entire database or certain files or filegroups only in
that database.
For more details on the above commands, please consult your SQL Server
Books online.
Hope this helps,
--
Eric Cárdenas
SQL Server senior support professional