Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Filegroups in SQL Server 2005

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (11)   |   Related Tips: 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



Last Update: 11/16/2006


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Wednesday, September 10, 2008 - 5:49:56 AM - hoffmanc Read The Tip

 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

Saturday, July 14, 2012 - 1:28:22 PM - Dinesh Dattatray Vishe Read The Tip

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...................


Saturday, July 14, 2012 - 2:40:29 PM - Jeremy Kadlec Read The Tip

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 - 2:50:57 PM - Dinesh Dattatray Vishe Read The Tip

@Jeremy ,

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

It working fine.Thank for feedback

 


Saturday, July 14, 2012 - 2:56:35 PM - Dinesh Dattatray Vishe Read The Tip

@ 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 - 3:39:58 PM - Dinesh Dattatray Vishe Read The Tip

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.

 


Sunday, July 15, 2012 - 1:05:59 PM - Jeremy Kadlec Read The Tip

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


Tuesday, November 27, 2012 - 12:22:59 PM - Bobby Read The Tip

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


Wednesday, November 28, 2012 - 2:05:38 PM - Jeremy Kadlec Read The Tip

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


Wednesday, November 28, 2012 - 3:12:21 PM - Bobby Read The Tip

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 - 4:50:39 PM - Jeremy Kadlec Read The Tip

Bobby,

Happy to hear it...

Thank you,
Jeremy Kadlec



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.