SQL SERVER – Create Multiple Filegroup For Single Database

I am elated to receive hundreds of emails every day from my readers. My tight work schedule refrains me from answering all your questions, but I do try my best to entertain them whenever I can. Today’s post revolves around a question I received a number of times last year but never blogged on it. On positive side, you are reading about that interesting subject today.

The question is – How to create multiple filegroup for any database?

To find solution to this query, we will go through the following four cases.

1) Creating New Database

a) Using T-SQL
b) Using SSMS

2) Updating Existing Database and Adding Filegroup

a) Using T-SQL
b) Using SSMS

Let us go over each of the cases one by one.

1) Creating New Database
a) Using T-SQL

CREATE DATABASE [FGTest] ON  PRIMARY
( NAME = N'FGTest',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FGTest.mdf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB ),
FILEGROUP [Secondary]
( NAME = N'FGTest_2',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FGTest_2.ndf' ,
SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'FGTest_log',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\FGTest_log.ldf' ,
SIZE = 1024KB , FILEGROWTH = 10%)
GO

b) Using SSMS

2) Updating Existing Database and Adding Filegroup

Updating existing database is much simpler compared with adding filegroup to new database. Let us take a look at how quickly it can be completed in a few simple steps.

a) Using T-SQL
We will add a Third filegroup to the same database where we added secondary filegroup in the above example.

USE [master]
GO
ALTER DATABASE [FGTest] ADD FILEGROUP [Third]
GO

b) Using SSMS
Right click on the database and go to properties and filegroup. Add new filegroup by simply adding name.

I encourage all my readers to keep mailing me send in your questions and doubts. I will certainly take them up at some point of time.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

34 thoughts on “SQL SERVER – Create Multiple Filegroup For Single Database

  1. Pingback: SQL SERVER – List All Objects Created on All Filegroups in Database Journey to SQL Authority with Pinal Dave

  2. Hello Dave,

    I am New to MS sql, But I find interesting in Database servers, As I would like to learn the Concepts of Administration the SQL server as will as how efficient can I make the server work.

    Can you Please Help me in it

    regards
    SreeMon

  3. Hi Dave,

    I’m very new to this technology. In the above you told about
    Create Multiple Filegroup For Single Database..i have small query how to add table to this new filegroup…

  4. Filegroup is based on database.Is it wisable to partition table into hortizontally or can we make a different filegroup for single Table? I know its funny to ask.

  5. Hello Joggee,

    Asking question is not funny and you asked a very good question. You are comparing two methods (partitioning and storing a table on separate filegroup) to split the table data over multiple disks files.
    If a filegroup has multiple files and you store a table on this filegroup, table’s data would be splitted over all files. This is a method to improve the I/O performance of a table. But data splitting over multiple files is not visible to user and we can not use this method to work upon a section on data.
    Partitioning is another way to split the data over multiple filegroup (and internally over multiple files). But in this method we know which record is stored in which filegroup. The partitions of data are visible to user and we can perform few tasks like SELECT, index defragmentation, etc. on a single partition also.

    Regards,
    Pinal Dave

  6. Hello Zafar,

    At the time of creation you can specify the filegroup as below:

    CREATE TABLE tableName (columns specification…) ON filegroup

    An existing table (heap) can be moved to another filegroup by creating the clustered index on new filegroup as below:

    CREATE CLUSTERED INDEX indexName ON tableName (column…) ON filegroup

    Now if the clustered index is not required then it can be dropped.

    If table already have a clustered index then create the clustered index with DROP_EXISTING option.

    Regards,
    Pinal Dave

  7. Hi,

    I want to add file group dynamically.

    Ex.
    When date change i execute script for create file group as per date.

    Declare @FileName varchar(50)
    Set @FileName=convert(varchar,getdate(),112)
    print @FileName
    ALTER DATABASE VehicleTrackingSystem ADD FILEGROUP [@FileName]

    Above script create file group with name @FileName which is wrong.

    Please look in this problem.

    Regards,
    Parthesh

  8. Hi! Can you help me: i want to move all my tables to new filegroup, i’ve moved all tables with Primary key, but what shoud i do with tables without PK and tables with text columns? sorry for my english

  9. Hi Dave,

    Will you please explain what is the relationship between filegroup and table is it a One-to-One or One-to-many. Can we write multiple tables into single filegroup which has multiple files?? If we can how to retrieve multiple tables data from a single file which is in a filegroup?? Is my question is ODD?

    for example i have a database which has three large tables A ,B and C. i want to partition month by month data for june,july,aug,sep for each table A,B and C . To achieve this do i need to create 12 files and 12 filegroups for the 3 tables?? Please Explain.

    Thanks
    Siri

  10. Hi Dave,

    I am working with win server 2003 with SQL Server 2005. I wanted to try back ups(just for working knowledge sake) and due to limited resources i wanted to try this using 2 instance of SQL rather than 2 different servers.

    I had database on default instance and i wanted to restore it on named instance 1. Since i want to maintain the same name of the database i tried to create a database with same name and i got an error which i was expecting because i already had a database from named instance on the PRIMARY file group. Hence while creating the database on the instance 1 i created a new file group and changed it to default. However i had to create a new file called mydatabase_1.mdf and only then the process was sucessful. However while i was creating the datbase it still had the mydatabase.mdf and mydatabase_log.ldf, the third file i created was mydatabase_1.

    I am confused as to which will would have been present on which file group.

  11. Should have thought better. Each instance has its own PRIMARY file group and hence databases with same name could be created. I am not sure why SQL threw me an error but i did the entire process again and i was able to create and back up the database as i desired.

  12. hi sir
    i have a WINDOS SERVER 2008 AND MICROSOFT SQL CAL 2008 R2 SNGL.
    I HAVE 10 USERS AND I WANT TO KNOW HOW CAN I MAKAE
    A DATABASE FOR EACH USER CAN YOU HELP ME PLEASE

  13. Thanks for the article….The screenshots really helped..
    Please give and article for how can we online restore from filegroup.

    Thanks Dave.

  14. Hi, i have created a secondary filegroup, but this was created with the same size of primary filegroup and i dont know what is the problem, now my database meisures is 6gb and before it was 3gb and sql not allow me delete the secondary filegroup. Can you help me?
    excuse my bad languaje, mi native is spanish.

  15. hello this is zuber,
    i would like to know “is this possible to store aparticular table under particular file of filegroup” and please tell me how to backup particular filegroup

  16. U just write the code and finish up the article.

    Would you plz care about explaining the code you write ?

    For example, for any learner, its vital to understand what is the difference between “NAME” & “FILENAME”

    I know the difference after reading http://msdn.microsoft.com/en-us/library/ms176061.aspx that

    “Name” is logical_file_name &
    “Filename” is os_file_name

    Thanks

  17. This is a Very Interesting Article..i have read and this has open my mind to some ideas.. However i have a question.

    1) I have a DB with a tot of tables (lets say 500 MB)
    2) One of the tables is an Images table.. so is becoming too big..(Lets Say 5GB)

    I need/want to split into 2 dbs to improve performace and manage space..

    If i separate the images table into another hard disk i will have 2 dbs.. that i can joing using the simple filegroup discussed here..

    BUT now i have a question..

    What about backups?
    - if i do a backup of the database will that hold the 2 databases into a single dump file?
    - can make a back up excludings the images database?

    what if i want to automate backups in separate schedules of the 2 databases, into different dump files.

    Edwin

  18. Hi,
    I have a question. I am trying to Create a partition table with the following settings :
    1FileGroup per month
    One Partition per Day
    and Now I want to link all Partitions of one month to its corresponding Filegroup. Could you suggest me the syntax for the Partition Function and Partition Scheme in this case.
    Thanks!
    J

  19. Hi,
    I have a question. I am trying to Create a partition table with the following settings :
    1FileGroup per month
    One Partition per Day
    and Now I want to link all Partitions of one month to its corresponding Filegroup. Could you suggest me the syntax for the Partition Function and Partition Scheme in this case.

    I tried the following, but it does not work :

    CREATE PARTITION FUNCTION PF_tblLogHistory2013 (DATETIME) AS RANGE RIGHT FOR VALUES ( ’20130102′ ’20130103′ ’20130104′ ’20130105′ ’20130106′ ’20130107′ ’20130108′ ’20130109′ ’20130110′ ’20130111′ ’20130112′ ’20130113′ ’20130114′ ’20130115′ ’20130116′ ’20130117′ ’20130118′ ’20130119′ ’20130120′ ’20130121′ ’20130122′ ’20130123′ ’20130124′ ’20130125′ ’20130126′ ’20130127′ ’20130128′ ’20130129′ ’20130130′ ’20130131′ , ’20130201′ ’20130202′ ’20130203′ ’20130204′ ’20130205′ ’20130206′ ’20130207′ ’20130208′ ’20130209′ ’20130210′ ’20130211′ ’20130212′ ’20130213′ ’20130214′ ’20130215′ ’20130216′ ’20130217′ ’20130218′ ’20130219′ ’20130220′ ’20130221′ ’20130222′ ’20130223′ ’20130224′ ’20130225′ ’20130226′ ’20130227′ ’20130228′ , ’20130301′ ’20130302′ ’20130303′ ’20130304′ ’20130305′ ’20130306′ ’20130307′ ’20130308′ ’20130309′ ’20130310′ ’20130311′ ’20130312′ ’20130313′ ’20130314′ ’20130315′ ’20130316′ ’20130317′ ’20130318′ ’20130319′ ’20130320′ ’20130321′ ’20130322′ ’20130323′ ’20130324′ ’20130325′ ’20130326′ ’20130327′ ’20130328′ ’20130329′ ’20130330′ ’20130331′ , ’20130401′ ’20130402′ ’20130403′ ’20130404′ ’20130405′ ’20130406′ ’20130407′ ’20130408′ ’20130409′ ’20130410′ ’20130411′ ’20130412′ ’20130413′ ’20130414′ ’20130415′ ’20130416′ ’20130417′ ’20130418′ ’20130419′ ’20130420′ ’20130421′ ’20130422′ ’20130423′ ’20130424′ ’20130425′ ’20130426′ ’20130427′ ’20130428′ ’20130429′ ’20130430′ , ’20130501′ ’20130502′ ’20130503′ ’20130504′ ’20130505′ ’20130506′ ’20130507′ ’20130508′ ’20130509′ ’20130510′ ’20130511′ ’20130512′ ’20130513′ ’20130514′ ’20130515′ ’20130516′ ’20130517′ ’20130518′ ’20130519′ ’20130520′ ’20130521′ ’20130522′ ’20130523′ ’20130524′ ’20130525′ ’20130526′ ’20130527′ ’20130528′ ’20130529′ ’20130530′ ’20130531′ , ’20130601′ ’20130602′ ’20130603′ ’20130604′ ’20130605′ ’20130606′ ’20130607′ ’20130608′ ’20130609′ ’20130610′ ’20130611′ ’20130612′ ’20130613′ ’20130614′ ’20130615′ ’20130616′ ’20130617′ ’20130618′ ’20130619′ ’20130620′ ’20130621′ ’20130622′ ’20130623′ ’20130624′ ’20130625′ ’20130626′ ’20130627′ ’20130628′ ’20130629′ ’20130630′ , ’20130701′ ’20130702′ ’20130703′ ’20130704′ ’20130705′ ’20130706′ ’20130707′ ’20130708′ ’20130709′ ’20130710′ ’20130711′ ’20130712′ ’20130713′ ’20130714′ ’20130715′ ’20130716′ ’20130717′ ’20130718′ ’20130719′ ’20130720′ ’20130721′ ’20130722′ ’20130723′ ’20130724′ ’20130725′ ’20130726′ ’20130727′ ’20130728′ ’20130729′ ’20130730′ ’20130731′ , ’20130801′ ’20130802′ ’20130803′ ’20130804′ ’20130805′ ’20130806′ ’20130807′ ’20130808′ ’20130809′ ’20130810′ ’20130811′ ’20130812′ ’20130813′ ’20130814′ ’20130815′ ’20130816′ ’20130817′ ’20130818′ ’20130819′ ’20130820′ ’20130821′ ’20130822′ ’20130823′ ’20130824′ ’20130825′ ’20130826′ ’20130827′ ’20130828′ ’20130829′ ’20130830′ ’20130831′ , ’20130901′ ’20130902′ ’20130903′ ’20130904′ ’20130905′ ’20130906′ ’20130907′ ’20130908′ ’20130909′ ’20130910′ ’20130911′ ’20130912′ ’20130913′ ’20130914′ ’20130915′ ’20130916′ ’20130917′ ’20130918′ ’20130919′ ’20130920′ ’20130921′ ’20130922′ ’20130923′ ’20130924′ ’20130925′ ’20130926′ ’20130927′ ’20130928′ ’20130929′ ’20130930′ , ’20131001′ ’20131002′ ’20131003′ ’20131004′ ’20131005′ ’20131006′ ’20131007′ ’20131008′ ’20131009′ ’20131010′ ’20131011′ ’20131012′ ’20131013′ ’20131014′ ’20131015′ ’20131016′ ’20131017′ ’20131018′ ’20131019′ ’20131020′ ’20131021′ ’20131022′ ’20131023′ ’20131024′ ’20131025′ ’20131026′ ’20131027′ ’20131028′ ’20131029′ ’20131030′ ’20131031′ , ’20131101′ ’20131102′ ’20131103′ ’20131104′ ’20131105′ ’20131106′ ’20131107′ ’20131108′ ’20131109′ ’20131110′ ’20131111′ ’20131112′ ’20131113′ ’20131114′ ’20131115′ ’20131116′ ’20131117′ ’20131118′ ’20131119′ ’20131120′ ’20131121′ ’20131122′ ’20131123′ ’20131124′ ’20131125′ ’20131126′ ’20131127′ ’20131128′ ’20131129′ ’20131130′ , ’20131201′ ’20131202′ ’20131203′ ’20131204′ ’20131205′ ’20131206′ ’20131207′ ’20131208′ ’20131209′ ’20131210′ ’20131211′ ’20131212′ ’20131213′ ’20131214′ ’20131215′ ’20131216′ ’20131217′ ’20131218′ ’20131219′ ’20131220′ ’20131221′ ’20131222′ ’20131223′ ’20131224′ ’20131225′ ’20131226′ ’20131227′ ’20131228′ ’20131229′ ’20131230′ ’20131231′ )
    USE JyotiTest
    CREATE PARTITION SCHEME PS_tblLogHistory2013 AS PARTITION PF_tblLogHistory2013 TO (FG_JyotiTest_tblLogHistory2013_January,FG_JyotiTest_tblLogHistory2013_February,FG_JyotiTest_tblLogHistory2013_March,FG_JyotiTest_tblLogHistory2013_April,FG_JyotiTest_tblLogHistory2013_May,FG_JyotiTest_tblLogHistory2013_June,FG_JyotiTest_tblLogHistory2013_July,FG_JyotiTest_tblLogHistory2013_August,FG_JyotiTest_tblLogHistory2013_September,FG_JyotiTest_tblLogHistory2013_October,FG_JyotiTest_tblLogHistory2013_November,FG_JyotiTest_tblLogHistory2013_December)

  20. i have created filegroups on seperate disks. ..using sql 2k12. ..however when i creat new tables and load only the primary has data. ..the secondary has very little…ideas?

  21. Hello,

    I am running FCS and the Filegroup is always full.
    I have Primary file with a file EE_DATA *EEA_DATA.mdf)
    I created a Secondary file group with a file EEA_DATA1 (EEA_DATA1.ndf where there is space
    But the Reindex job is constantly faoiling Filegroup primary failing…
    it does not seem the job is looking at the Secondary Filegroup!!! any step to do so?
    Any idea?
    Thanks,
    Dom

  22. Dear sir I want to know how to find the size of particular table in a database. I want to find the size of table not the size of the database

  23. @Chakravarthi:

    may be you are looking for this:

    set nocount on
    declare @indexes table(
    QualifiedName nvarchar(512),
    IndexId int,
    FGName nvarchar(128),
    Type nvarchar(50),
    NumKeys int,
    IndexKB numeric(28,0),
    UsedKB numeric(28,0),
    FreeKB numeric(28,0),
    Rows numeric(28,0),
    RowModCtr numeric(28,0),
    OrigFillFactor int,
    tableid bigint
    )

    insert into @indexes
    select
    db_name() + ‘.’ + isnull(su.name,”) + ‘.’ + so.name + ‘.’ + isnull(i.name,”) QualifiedName,
    i.index_id IndexId,
    (select isnull(name,”) from sys.filegroups where data_space_id = i.data_space_id) FGName,
    case
    when so.type = ‘V’ then ‘Indexed View: ‘
    else ”
    end +
    case
    when i.index_id = 0 then ‘Heap’
    when i.index_id = 1 then ‘Clustered’
    else ‘Non Clustered’
    end Type,
    0 NumKeys,
    a.used_pages* 8 IndexKB,
    CASE
    When a.type 1 Then a.used_pages * 8
    When p.index_id 1
    group by tableid
    ) t on t.tableid = i.tableid
    and i.IndexId <= 1
    order by
    IndexKB desc

  24. Pingback: SQL SERVER – Weekly Series – Memory Lane – #031 | Journey to SQL Authority with Pinal Dave

  25. Hi Pinal ,
    I am encountering an issue with my Database (SQL Server 2008 R2 )

    Error Message :
    Could not allocate space for object ‘dbo.XXXXXXXX’.’PK_XXXXXXX’ in
    database ‘DemoDB’ because the ‘PRIMARY’ filegroup is full.
    Create disk space by deleting unneeded files, dropping objects in the filegroup,
    adding additional files to the filegroup,
    or setting autogrowth on for existing files in the filegroup.

    Will Adding new file (DemoDB.ndf) help me to resolve this issue?
    Or Do you have any suggestion to resolve this issue?
    Please reply soon, I need to solution very urgently.

  26. Hi Dave,
    thank you so much for your tutorial, it was really helpfull.
    i want to create 2 data files on a group named permanant : c:\perm1.ndf and d:\perm2.ndf
    and i want to create another group named temporaire on which i’m gonna create also 2 data files : c:\temp1.ndf and d:\temp2.ndf.
    would you please help me to solve this because when i try to create two groups i made syntax errors.
    THANKS :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s