Filegroups in SQL Server 2005

By:   |   Updated: 2006-11-16   |   Comments (11)   |   Related: More > Database Administration


Problem

With the anticipated growth of my database what native features does SQL Server 2005 have to help me scale to support a large amount of data?  I have a relatively small database today, but based on my capacity planning, I anticipate that the growth will be 3 to 7 times my current database in the next 18 months.  What steps can I take from a planning perspective to ease this growth?

Solution

Many native SQL Server 2005 options exist to help support database growth.  From a planning perspective, one of the first topics that should be addressed is filegroups.  A filegroup can be considered a logical storage unit to house database objects that maps to a file system file or multiple files. 

By default each database has a primary filegroup to support the system and user objects.  To help support the needed growth and assumed performance, additional disk drives can be allocated to the server.  Next, new filegroups and files can be created on the new disk drives.  Then objects can be moved to these filegroups as a means to spread the IO over additional disks.

How can I create a new filegroups?

USE CustomerDB_OLD;
GO
ALTER DATABASE CustomerDB_OLD
ADD FILEGROUP FG_ReadOnly
GO

How can I add files to a filegroup?

ALTER DATABASE CustomerDB_OLD
ADD FILE 
( 
   NAME = FG_READONLY1,
   FILENAME = 'C:\CustDB_RO.ndf',
   SIZE = 5MB,
   MAXSIZE = 100MB,
   FILEGROWTH = 5MB
) TO FILEGROUP FG_READONLY;
GO

How can I create objects in the new filegroup?

-- Table
CREATE TABLE dbo.OrdersDetail
(
   OrderID int NOT NULL,
   ProductID int NOT NULL,
   CustomerID int NOT NULL, 
   UnitPrice money NOT NULL,
   OrderQty smallint NOT NULL
)
ON FG_READONLY 

-- Index
CREATE INDEX IDX_OrderID 
ON dbo.OrdersDetail(OrderID) ON FG_READONLY
GO

How can I move an object from the primary file group to another file group?

To move an existing table with a clustered index, issue the following command: 

-- Table - The base table is stored with the
-- clustered index, so moving the clustered
-- index moves the base table
CREATE CLUSTERED INDEX IDX_ProductID ON dbo.OrdersDetail(ProductID) ON FG_ReadOnly
GO

To move a non-clustered index, issue the following command:

-- Non-clustered index
CREATE INDEX IDX_OrderID ON dbo.OrdersDetail(OrderID) 
WITH (DROP_EXISTING = ON)
ON FG_ReadOnly
GO

If the table does not have a clustered index and needs to be moved, then create the clustered index on the table specifying the new file group. This process will move the base table and clustered index to the new file group. Then the clustered index can be dropped.  Reference these commands:

-- Table without a clustered index + drop index
CREATE CLUSTERED INDEX IDX_ProductID ON dbo.OrdersDetail(ProductID) 
ON FG_ReadOnly
GO 

DROP INDEX IDX_ProductID ON dbo.OrdersDetail(ProductID)
GO

How can I determine which objects exist in a particular filegroup?

SELECT o.[name], o.[type], i.[name], i.[index_id], f.[name]
FROM sys.indexes i
INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id
INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]
WHERE i.data_space_id = 2 --* New FileGroup*
GO

What are possible filegroup configurations?

Although the sky is the limit and application as well as hardware availability drive most decisions, below outlines some options that can be considered when designing a filegroup configuration:

  • Option 1
    • Data filegroup
    • Index filegroup
  • Option 2
    • Read only tables filegroup
    • Read-write tables filegroup
    • Index filegroup
  • Option 3
    • Read only tables filegroup
    • Read-write tables filegroup
    • Index filegroug
    • Key table 1 filegroup
    • Key table 2 filegroup
    • Key table 3 filegroup

Do other issues exist that filegroups can solve?

Yes - Based on your application, filegroups can be created to resolve IO performance problems by spreading the database over additional spindles alleviating disk queuing.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2006-11-16

Comments For This Article




Wednesday, November 28, 2012 - 4:50:39 PM - Jeremy Kadlec Back To Top (20609)

Bobby,

Happy to hear it...

Thank you,
Jeremy Kadlec

Wednesday, November 28, 2012 - 3:12:21 PM - Bobby Back To Top (20608)

Jeremy, Thanks.  That did it.  I guess the root of my question turned out to be - how the statement works if you already have the index.  Dropping it first makes perfect sense.

We have a 600GB database with growing backup times and if I'm proposing multiple filegroups I want to make sure I can implement them if it's approved.

Thanks again!

Wednesday, November 28, 2012 - 2:05:38 PM - Jeremy Kadlec Back To Top (20605)

Bobby,

Let me see if I can break this down a little bit:

"Jeremy, There are a lot of discussions on this and yours might be the best. "

JTK - Thank you.  That makes my day.

 

"Regarding the snippet below, what if you already have a clustered index(es) on the table you want to move?"

JTK - You would need to drop the index first and then create it in the new filegroup.  Creating the index in the new filegroup will move the table and clustered index into the new file group.  Keep in mind the non clustered indexes can move to the new filegroup or reside in a different filegroup depending on your goal(s).

 

"Will this command try to create a duplicate index?"

JTK - No - You can only have 1 clustered index per table.

 

"Will it drop the existing one?"

JTK - No. The DROP INDEX command removes an existing index.

 

"If you would just expound on the way this statement works to help me understand how it will accomplish the move I would appreciate it! Thank you, Bobby"

JTK - Sure...Let me know if this helps.

Thank you,
Jeremy Kadlec

Tuesday, November 27, 2012 - 12:22:59 PM - Bobby Back To Top (20569)

Jeremy, There are a lot of discussions on this and yours might be the best.  Regarding the snippet below, what if you already have a clustered index(es) on the table you want to move?  Will this command try to create a duplicate index?  Will it drop the existing one?  If you would just expound on the way this statement works to help me understand how it will accomplish the move I would appreciate it!  Thank you, Bobby

How can I move an object from the primary file group to another file group?

To move an existing table with a clustered index, issue the following command:

-- Table - The base table is stored with the
-- clustered index, so moving the clustered
-- index moves the base table
CREATE CLUSTERED INDEX IDX_ProductID ON dbo.OrdersDetail(ProductID)
ON FG_ReadOnly
GO


Sunday, July 15, 2012 - 1:05:59 PM - Jeremy Kadlec Back To Top (18528)

Dinesh,

It sounds like you may have a few questions based on your 2 posts.  Let me see if I can try to answer them.

You can create a new file group and move specific tables to the file group.

You can issue differential backups, but only the extents where data has change should be backed up.

Without knowing the application, drive layout, etc.  I assume your differential backup should meet your needs because only extents that have had data changes would be backed up. 

Keep in mind for a restore process you would need to restore your last full backup and then your most recent differential backup.

HTH.

Thank you,
Jeremy Kadlec

Saturday, July 14, 2012 - 3:39:58 PM - Dinesh Dattatray Vishe Back To Top (18524)

I am doing Differencial backup of whole database.But only 2-3 table is updated daily basis.While differncial backup whole database resource is utiliesed,It this tables will stored into new file system then It will reduced many resoures.

Waiting your reply.

 

Saturday, July 14, 2012 - 2:56:35 PM - Dinesh Dattatray Vishe Back To Top (18523)

@ JEremy,

There are 2-3  table which are daily updated. I want alllow this table into new filegroup.SO I can take backup of this filegroup daily basis.It is feasible way or any other soltion you have ??

Please guide me I am waiting your solution..

 

Saturday, July 14, 2012 - 2:50:57 PM - Dinesh Dattatray Vishe Back To Top (18521)

@Jeremy ,

I had admistator right to c: drive and check also in D: .

It working fine.Thank for feedback

 

Saturday, July 14, 2012 - 2:40:29 PM - Jeremy Kadlec Back To Top (18520)

Dinesh,

Based on the information provided, it looks like you do not have rights to C:\.

Do you have rights to any other drives (D:\, E:\, F:\, etc.) on the machine?

If not, consider creating a new directory on the C drive (C:\SQLServerData\ and C:\SQLServerLog\)to store your databases.\

HTH.

Thank you,
Jeremy Kadlec

 

Saturday, July 14, 2012 - 1:28:22 PM - Dinesh Dattatray Vishe Back To Top (18519)

While creating file group in C drive following error occur  

CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'C:\tesy_file.ndf'.

Please help ME...................

Wednesday, September 10, 2008 - 5:49:56 AM - hoffmanc Back To Top (1773)

 I have a table that easily takes up 80% of the space of the database, and I need a way to pull down a backup of everything but this table.  I followed the referenced tutorial, but when I look at my new file, it looks no bigger than the initial size I specified, when it should be closer to 100x that size.

 Here is exactly what I executed:

 

USE chrisdb;
GO
ALTER DATABASE chrisdb
ADD FILEGROUP FG_Attachments
GO

ALTER DATABASE chrisdb
ADD FILE
(
NAME = FG_Attachments,
FILENAME = 'C:\Chris_Attachments.ndf',
SIZE = 50MB,
MAXSIZE = 100GB,
FILEGROWTH = 10MB
) TO FILEGROUP FG_Attachments;
GO

alter table dbo.IncidentAttachments
drop constraint pk_incidentattachments with
(move to FG_Attachments)
GO 

alter table dbo.incidentattachments
add constraint pk_incidentattachments 
primary key(incidentattachmentid)
go














get free sql tips
agree to terms