Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Using Multiple Filegroups for a Database and Changing the Default Filegroup


By:   |   Read Comments (2)   |   Related Tips: More > Database Administration

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


Problem

SQL Server allows you to create additional filegroups to spread storage of data and indexes to different disks to improve I/O performance.  The issue is that when creating new data storage objects we are required to explicitly point to the new filegroup. Is there any option to use the new filegroup by default without explicitly specifying the filegroup when creating new tables and indexes?

Solution

Before we get started, let me explain some terminology:

  • Database - is made up of multiple files for database objects and transaction logs
  • File - a physical file that is used to store database objects or transaction logs
  • Filegroup - this is a container that is made up of one or more files to store database objects
  • PRIMARY filegroup - this is the default filegroup that is created when you create a new database.
  • Default filegroup - when creating new tables and indexes this specifies which filegroup is the default if a filegroup is not specified

So based on this when creating a new database, the primary file group of a database is the default filegroup and the primary data file is created in it. When a table or index is created without specifying a filegroup, all pages will be allocated from the default filegroup. Note: only one filegroup at a time can be the default filegroup.

By default, the primary filegroup is the default filegroup, so all new objects are placed in this filegroup. However we can change the default filegroup to any other filegroup at any time. When you have multiple filegroups, changing the default filegroup from PRIMARY to another one is a good idea, because it eliminates the need to specify the filegroup when objects are created and makes sure that new objects are created in the correct filegroup.

Example

Let's analyze the problem for which we are required to change the default filegroup from primary to something else.

The following script creates a database and then adds additional filegroups. Although additional filegroups can be created when creating a database, I added the filegroups later for simplicity and illustration. So in this case, the primary filegroup will be the default filegroup.

-- Script 1: Create DB and add additional file group
-- If DB pre exists then drop it
IF exists (SELECT * FROM sys.databases WHERE NAME = 'MSSQLTip')
USE MASTER
DROP DATABASE MSSQLTip
GO
-- Create new DB
CREATE DATABASE MSSQLTip
GO
-- Add file groups to DB
ALTER DATABASE MSSQLTip ADD FILEGROUP FG1
ALTER DATABASE MSSQLTip ADD FILEGROUP FG2
GO
-- Verify file groups in DB
USE MSSQLTip
GO 
SELECT groupName AS FileGroupName FROM sysfilegroups
GO

At this point we have three file groups primary, FG1 and FG2 in our new database. Any new table or index will be created in the primary filegroup which is the default unless we specify the filegroup to use.  If we want to create a data storage object in FG1 or FG2 then the only way is to specify the filegroup when creating the storage object.

Consider the following scenario where we want to create a table without specifying the filegroup. It is essential to provide explicit instructions for creating the data storage objects in other than the default filegroup. The only way to ensure the creation of new data storage objects on FG1 or FG2 without any additional consideration is to set the required filegroup as shown below.

-- Script 2: Create tables in file groups
-- tbl1 would be created on primary file group
CREATE Table tbl1 (ID int identity(1,1))
GO
-- tbl2 would be created on FG1
CREATE Table tbl2 (ID int identity(1,1), fname varchar(20))
ON FG1
GO
-- Verify file group of tbl1
sp_help tbl1
GO
-- Verify file group of tbl2
sp_help tbl2
GO

Now that we created the table on the new filegroup we can try to insert data as follows:

INSERT INTO tbl2 (fname) values ('Atif')
GO

Since we did not specify a file for this filegroup we get this error message:

Msg 622, Level 16, State 3, Line 1
The filegroup "FG1" has no files assigned to it. Tables, indexes, text columns, ntext columns, and image columns cannot be populated on this filegroup until a file is added.

Another issue we have since we do not have a file associated with this filegroup is if we try to change FG1 to be the default filegroup as shown below:

ALTER DATABASE MSSQLTip MODIFY FILEGROUP FG1 DEFAULT
GO

We will get this error message, because a file group cannot be set as the default unless it has at least one data file.

Msg 5050, Level 16, State 1, Line 1
Cannot change the properties of empty filegroup 'FG1'. The filegroup must contain at least one file.

 Creating the Data File

A data file may be placed in a single filegroup and cannot be shared among filegroups. Files may be placed in the filegroup at DB creation time or at a later time. In the following script we create secondary data files for each filegroup (FG1 and FG2). (Note: if you do this on your server you will need specify the correct path for the file creation.)

-- Script 3: Add data files to file groups
-- Add data file to FG1
ALTER DATABASE MSSQLTip
ADD FILE (NAME = MSSQLTip_1,FILENAME = 'F:\DBs\2K5\MSSQLTip_1.ndf')
TO FILEGROUP FG1
GO
-- Add data file to FG2
ALTER DATABASE MSSQLTip
ADD FILE (NAME = MSSQLTip_2,FILENAME = 'F:\DBs\2K5\MSSQLTip_2.ndf')
TO FILEGROUP FG2
GO
-- Verify files in file groups
USE MSSQLTip
GO
sp_helpfile
GO
  

Setting New Default Filegroup

After creating the new files for filegroups FG1 and FG2, we can change the default filegroup and verify the new default by running the code below.  

--Script 4: Set FG1 as default file group
-- Set FG1 as default file group
ALTER DATABASE MSSQLTip MODIFY FILEGROUP FG1 DEFAULT
GO
-- Create a table without specifying file group
Create table table3 (ID TINYINT)
GO
--Verify the file group of table3 is FG1
sp_help table3
GO
-- insert some data to make sure no errors
insert into table3 values (10)
GO

Using sp_help shows that table3 is created in FG1 even without specifying FG1 when creating the table.

Now let's set FG2 as the default filegroup using SSMS. Right click on the MSSQLTip database and click on Properties. Go to the filegroups page and set FG2 as the default as shown below.

Set default file group through SSMS GUI

Checking the Default Filegroup

In addition to using SSMS to find what filegroup is the default, we can also check the default filegroup by querying sysfilegroups as shown below.

--Script 5: verify default filegroup
USE MSSQLTip
GO
SELECT groupname AS DefaultFileGroup FROM sysfilegroups
WHERE convert(bit, (status & 0x10)) = 1
GO

Summary

Setting the default filegroup does not stop you from using other filegroups it just means that you will need to specify the correct filegroup when creating a table or index otherwise it will be created on whatever filegroup is set as the default.

Next Steps
  • Members of db_owner fixed database role can change the default filegroup.
  • If used effectively, utilizing multiple filegroups and files you can improve performance by spreading the I/O load. Analyze your current and new databases to take benefit of having multiple filegroups.  Read this tip for more information.
  • You can also move storage objects from one filegroup to another.  Read this tip for more information.
  • Click here to read more about database file and filegroup architecture


Last Update:


signup button

next tip button



About the author
MSSQLTips author Atif Shehzad Atif Shehzad is a passionate SQL Server DBA, technical reviewer and article author.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Friday, August 01, 2014 - 4:29:11 AM - Steve Sarre Back To Top

 

Hi Atif

 

Old post but I think

IF exists (SELECT * FROM sys.databases WHERE NAME = 'MSSQLTip')
USE MASTER
DROP DATABASE MSSQLTip
GO
should be
IF exists (SELECT * FROM sys.databases WHERE NAME = 'MSSQLTip')
BEGIN USE MASTER DROP DATABASE MSSQLTip END GO

HTH
Steve

Thursday, December 19, 2013 - 1:06:10 PM - Stu Back To Top

What significance are filegrups, when using Hitachi Virtual Storage SAN/fibrechannel. I mean, you have no idea what spindle gets impacted.


Learn more about SQL Server tools