Filegroups

Hello Everyone,
Few weeks ago I asked for suggestions and recommendation
on an issue we have with backing up a big SQL Server 2000
database (250+GB).
The backup process seems to be unmanagable and taking too
long (hours or days)!
I mentioned that we have this one table that consumes
more than 75% of the size of the database.
This table has three columns:
CREATE TABLE tb1 (
id int, --PK column
size int,
content Text
)
We are storing the content of email message body in
content column.
Some people suggested creating this particular table on a
different Filegroup (User-Defined Filegroup).
Back then I had no clue what Filegroups were and how to
create and manage Filegroups. I started reading about the
Filegroups in SQL Server Books Online.
I am not so sure but it seems that placing tables on
different Filegroups is not a common practice.
Now that i have a little understanding of the purpose of
the Filegroups, please let me know if this is the best
approach to reduce the time of a database backup.
Are there any disadvantages to create a table on a
different Filegroup besides managing backup files?
Thank you,
-Mitra
Hi Mitra,
Filegroups help in both backups and performance. There is plenty of
information on this in BOL or MSDN. However, keep in mind that there are
several things to consider that may or may not be right for your environment
like storage, network traffic, speed of recovery, what tables are considered
"Hot", tables that are frequently used in joins, etc.
For faster and smaller backups with or without implementing filegroups, you
might want to look at software called SQLLiteSpeed from Imceda
http://www.imceda.com/ . I use it on one of my 100+ Gig databases with good
results. A native backup of my database runs about 2+ hours and the .BAK
file size is close to 60 Gig. When I use SQLLiteSpeed on the same database,
the time it takes to run a backup is less than 45 minutes and the .BAK file
size is around 20 Gig.
Hope this helps.
Bryan Bitzer MCP
Senior Database Administrator
Marshall & Swift / Boeckh
www.msbinfo.com
"mitra fatolahi" <anonymous@.discussions.microsoft.com> wrote in message
news:038d01c49ab0$14c0ae70$a401280a@.phx.gbl...
> Hello Everyone,
> Few weeks ago I asked for suggestions and recommendation
> on an issue we have with backing up a big SQL Server 2000
> database (250+GB).
> The backup process seems to be unmanagable and taking too
> long (hours or days)!
> I mentioned that we have this one table that consumes
> more than 75% of the size of the database.
> This table has three columns:
> CREATE TABLE tb1 (
> id int, --PK column
> size int,
> content Text
> )
> We are storing the content of email message body in
> content column.
> Some people suggested creating this particular table on a
> different Filegroup (User-Defined Filegroup).
> Back then I had no clue what Filegroups were and how to
> create and manage Filegroups. I started reading about the
> Filegroups in SQL Server Books Online.
> I am not so sure but it seems that placing tables on
> different Filegroups is not a common practice.
> Now that i have a little understanding of the purpose of
> the Filegroups, please let me know if this is the best
> approach to reduce the time of a database backup.
> Are there any disadvantages to create a table on a
> different Filegroup besides managing backup files?
> Thank you,
> -Mitra