SQL SERVER – Create Multiple Filegroup For Single Database

May 31, 2009

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 (https://blog.sqlauthority.com)

SQL Scripts, SQL Server Management Studio

SQL SERVER – Difference Between Candidate Keys and Primary Key

SQL SERVER – List All Objects Created on All Filegroups in Database

42 Comments. Leave new

Paul
April 4, 2012 1:36 am
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 https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-transact-sql?view=sql-server-2017 that
“Name” is logical_file_name &
“Filename” is os_file_name
Thanks
Reply
smi
April 5, 2012 12:54 pm
You are my Guru dear.
Reply
Edwin Blancovitch
June 4, 2012 8:51 pm
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
Reply
Jyoti
June 18, 2012 11:56 pm
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
Reply
Jyoti
June 19, 2012 12:03 am
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)
Reply
chuck
October 3, 2012 8:11 am
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?
Reply
Dominique
December 8, 2012 5:05 am
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
Reply
Chakravarthi
April 4, 2013 3:17 pm
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
Reply
Nullzone
May 2, 2013 7:29 pm
@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
Reply
Francis
July 10, 2013 12:30 pm
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.
Reply
Rowshan
October 24, 2013 1:08 am
Thanks for your valuable resources.
Reply
selva
November 3, 2013 5:00 pm
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 :)
Reply
Nagendra.K
December 10, 2014 6:11 pm
Thanks for the article.. it helps a lot
Reply
Md. Shahid
January 21, 2016 4:08 pm
Dave Sir,
I appreciated your experience and the way of describing in each blogs.
I really want to say thanks.
Reply
RAHUL
February 26, 2016 11:11 pm
CREATE DATABASE MYDEMO
ON(NAME=’ABC’,FILENAME=’C:\DFILES\MYDEMO.mdf’,SIZE=5MB,MAXSIZE=20MB,FILEGROWTH=2MB)
LOG ON(NAME=’XYZ’,FILENAME=’C:\DFILESMYDEMO_LOG.LDF’,SIZE=4MB,MAXSIZE=10MB,FILEGROWTH=1MB)
—————————————————————————————-
ERROR: Incorrect syntax near the keyword ‘ON’.
IS IT OK THEN WHY DOES NOT WORKING?
PLS HELP ME..
Reply
Kaustubha
April 1, 2016 11:56 am
Dear Pinal,
Can you explain , how to use partition functionality in SQL server 2012 express ?
Reply
MUSHTAQ
April 19, 2016 4:23 pm
Dear Pinal,
Kindly explain backup and restore of a filegroup through MSSQL Studio
Thanks,
Mushtaq
Reply
sathiyaseelan
June 1, 2016 1:11 pm
If you kindly (would you not mind provide) correct the code to create multiple file groups in multiple servers for a single database. Mssql server code given below:
CREATE DATABASE MS_SQL_DB
ON PRIMARY
( NAME=’MyDB_Primary’,
FILENAME= ‘c:Program FilesMicrosoft SQL ServerMSSQLdataMyDB_Prm.mdf’, \local DBMS server
SIZE=4, MAXSIZE=10, FILEGROWTH=1),
FILEGROUP MSDB_FG1
( NAME = ‘MSsql_DB_FG1_Dat1’, FILENAME = ‘ \com2db :c:Program FilesMicrosoft SQL ServerMSSQLdataMyDB_FG1_1.ndf’, \com2db 2nd computer
SIZE = 1MB, MAXSIZE=10, FILEGROWTH=1), ( NAME = ‘MyDB_FG1_Dat2’,
FILEGROUP MSDB_FG2
FILENAME = ‘come3dbc :Program FilesMicrosoft SQL ServerMSSQLdataMyDB_FG1_2.ndf’, \com3db 3rd computer
SIZE = 1MB, MAXSIZE=10, FILEGROWTH=1)
LOG ON
( NAME=’MyDB_log’,
FILENAME =
‘c:Program FilesMicrosoft SQL ServerMSSQLdataMyDB.ldf’, SIZE=1, MAXSIZE=10, FILEGROWTH=1)
GO
\ may be this can go to yet another computer too
oragle saye (sam@c: ) computername@path How do we reference it in MSSQL server
Reply
Ahamed
August 1, 2016 2:19 am
Is it possible restrict user access for a specific filegroup in a table partition?
Reply
S Rehman Ali
November 9, 2017 7:20 pm
Hi, how can I create it on the azure sql server
Reply