Blog Post

Procedure to Create New Filegroups and Files

,

Problem

For good database design, it is not idea to have everything in your PRIMARY filegroup so you can do partial backups, piecemeal restores, and for performance to separate your tables and indexes. You need different filegroups when looking at separating your indexes and tables and partitioning. Creating all these files and filegroups can become cumbersome and tedious if especially if you do a lot of partitioning and need to add them a periodic basis or have thousands of databases you need to add one file to.

Solution

This stored procedure gives you six different scenarios to create different files and filegroups based on your needs.  It can be expanded to more so it will be published on Github so any can add to it anything they see to improve it. The procedure will also create the directory for you if doesn’t exist and you have enough permissions.

USE DBA
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF (OBJECT_ID('dbo.FileFileGroup_Builder') IS NULL)
BEGIN
    EXEC ('CREATE PROCEDURE dbo.FileFileGroup_Builder AS SELECT 1 AS Stub;');
END
GO
----------------------------------------------------------------------------------
-- Procedure Name: dbo.FileFileGroup_Builder
--
-- Desc: This procedure is to create files and filegroups.
--
-- Parameters: 
--INPUT
--@Directory NVARCHAR(250),
--@TableName NVARCHAR(128) = NULL,
--@SchemaName NVARCHAR(128) = NULL,
--@Size INT = 0,
--@SizeBasedOnTable BIT = 0,
--@DatabaseName NVARCHAR(128),
--@ConcatDatabaseName BIT = 0,
--@ByDate BIT = 0,
--@ByPartitionNumber BIT = 0,
--@ByFilegroupName BIT = 0,
--@NumPartitions TINYINT = 0,
--@FileGroupName NVARCHAR(128), 
--@StartDate CHAR(6) = NULL,
--@EndDate CHAR(6) = NULL,
--@FileGrowthMB INT = 2048,
--@MaxSizeMB INT = 51200,
--@Debug BIT = 0
--
--OUTPUT
--
-- Auth: Tracy Boggiano
-- Date: 08/05/2015
--
-- Change History 
-- --------------------------------
-- Date - Auth: 09/14/2015 - Tracy Boggiano
-- Description: Add max size allowing for multiple files per filegroup.
----------------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[FileFileGroup_Builder]
(
@Directory NVARCHAR(250),
@TableName NVARCHAR(128) = NULL,
@SchemaName NVARCHAR(128) = NULL,
@Size INT = 0,
@SizeBasedOnTable BIT = 0,
@DatabaseName NVARCHAR(128),
@ConcatDatabaseName BIT = 0,
@ByDate BIT = 0,
@ByPartitionNumber BIT = 0,
@ByFilegroupName BIT = 0,
@NumPartitions TINYINT = 0,
@FileGroupName NVARCHAR(128), 
@StartDate CHAR(6) = NULL,
@EndDate CHAR(6) = NULL,
@FileGrowthMB INT = 2048,
@MaxSizeMB INT = 51200,
@Debug BIT = 0
)
AS
SET NOCOUNT ON;
--Figure out size of each file
DECLARE @TableMB INT;
DECLARE @FilegroupSizeMB INT;
DECLARE @FGName NVARCHAR(128);
DECLARE @SQL NVARCHAR(MAX);
DECLARE @ParmDefinition nvarchar(500);
DECLARE @NumMonths SMALLINT;
DECLARE @StartMonth TINYINT = RIGHT(@StartDate, 2);
DECLARE @StartYear SMALLINT = LEFT(@StartDate, 4);
DECLARE @EndMonth TINYINT = RIGHT(@EndDate, 2);
DECLARE @EndYear SMALLINT = LEFT(@EndDate, 4);
DECLARE @CurrentMonth TINYINT;
DECLARE @CurrentYear SMALLINT;
DECLARE @NumFiles TINYINT;
DECLARE @Stuff BIT;
DECLARE @OrgNumFiles TINYINT;
DECLARE @LogicalFilename NVARCHAR(128);
DECLARE @BeginLogicalFileName NVARCHAR(128);
IF @ByDate = 0 AND @ByPartitionNumber = 0 and @ByFilegroupName = 0
RAISERROR('Must specify one of the BY options', 11, -1)
IF @SizeBasedOnTable = 0 AND @Size = 0
RAISERROR('Must specify one of the size options', 11, -1)
IF @Debug = 1
EXEC master.sys.xp_create_subdir @Directory;
--Get months for start and end year
SET @NumMonths = 12 - @StartMonth + 1
SET @NumMonths = @NumMonths + @EndMonth

--Get months for years in between
SET @NumMonths = @NumMonths + (@EndYear - @StartYear - 1) * 12 
IF @SizeBasedOnTable = 1
BEGIN
SET @SQL = REPLACE(REPLACE(REPLACE(REPLACE('SELECT @TableMB = (SUM(a.total_pages) / 128) * 1.2
FROM {{@DatabaseName}}.sys.tables t
INNER JOIN {{@DatabaseName}}.sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN {{@DatabaseName}}.sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN {{@DatabaseName}}.sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN {{@DatabaseName}}.sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
t.NAME = "{{@TableName}}"
AND s.name = "{{@SchemaName}}"
AND i.index_id in (0,1)
GROUP BY 
t.Name, 
s.Name
ORDER BY 
s.Name, 
t.Name'
,'"','''')
,'{{@DatabaseName}}', @DatabaseName)
,'{{@TableName}}', @TableName)
,'{{@SchemaName}}', @SchemaName)
SET @ParmDefinition = N'@TableName NVARCHAR(257), @TableMB INT OUTPUT';
EXECUTE sp_executesql @SQL, @ParmDefinition, @TableName = @TableName, @TableMB = @TableMB OUTPUT;
END
ELSE
SET @TableMB = @Size
IF @ByPartitionNumber = 1 AND @SizeBasedOnTable = 1
SET @FilegroupSizeMB = ROUND(@TableMB / @NumPartitions, 0);
ELSE
IF @ByDate = 1 AND @SizeBasedOnTable = 1
SET @FilegroupSizeMB = ROUND(@TableMB / @NumMonths, 0);
ELSE
SET @FilegroupSizeMB = @TableMB
SET @OrgNumFiles = CEILING(@FilegroupSizeMB * 1.0 / @MaxSizeMB)
IF @OrgNumFiles = 1
SET @Stuff = 0
ELSE
SET @Stuff = 1 
IF @ByPartitionNumber = 1 AND @NumPartitions > 0
BEGIN
SET @SQL = ''
WHILE (@NumPartitions > 0)
BEGIN
SET @FGName = CONCAT(@FileGroupName, RIGHT('00' + CONVERT(VARCHAR(2),@NumPartitions), 2));
SET @BeginLogicalFileName = CASE @ConcatDatabaseName 
WHEN 1 THEN CONCAT(@DatabaseName, '_', @FGName)
ELSE @FGName
END
SET @SQL = @SQL + REPLACE(REPLACE(REPLACE(
N'
IF NOT EXISTS (
SELECT 1
FROM {{@DatabaseName}}.sys.filegroups
WHERE name = "{{@FGName}}"
)
BEGIN
ALTER DATABASE {{@DatabaseName}} ADD FILEGROUP {{@FGName}};
END
'
,'{{@FGName}}', @FGName)
,'{{@DatabaseName}}', @DatabaseName)
,'"', '''');
SET @NumFiles = @OrgNumFiles
WHILE @NumFiles > 0
BEGIN
SET @LogicalFilename = CONCAT(@BeginLogicalFileName, CASE @Stuff 
WHEN 1 THEN 
CASE LEN(@NumFiles) 
WHEN 1 THEN '_0' + CAST(@NumFiles AS VARCHAR(1))
ELSE '_' + CAST(@NumFiles AS VARCHAR(2))
END
ELSE ''
END)
SET @SQL = @SQL + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
N'
IF NOT EXISTS (
SELECT 1
FROM {{@DatabaseName}}.sys.database_files
WHERE
name = "{{@LogicalFileName}}"
)
BEGIN
ALTER DATABASE {{@DatabaseName}} ADD FILE (
NAME = "{{@LogicalFileName}}",
FILENAME = "{{@PhysicalFilePath}}\{{@LogicalFileName}}.ndf",
SIZE = {{@FilegroupSizeMB}}MB,
FILEGROWTH = {{@FileGrowthMB}}MB
)
TO FILEGROUP {{@FGName}};
END
'
, '{{@LogicalFileName}}', @LogicalFilename)
, '{{@PhysicalFilePath}}', @Directory)
, '{{@FilegroupSizeMB}}', CONVERT(NVARCHAR(8), CASE @Stuff
WHEN 0 THEN @FilegroupSizeMB
ELSE @MaxSizeMB
END))
, '{{@FGName}}', @FGName)
, '{{@FileGrowthMB}}', CONVERT(NVARCHAR(5), @FilegrowthMB))
, '{{@DatabaseName}}', @DatabaseName)
, '"', '''');
SET @NumFiles = @NumFiles - 1
END
SET @NumPartitions = @NumPartitions - 1;
END
END

IF @ByDate = 1 
BEGIN
SET @SQL = ''
SET @CurrentMonth = @StartMonth
SET @CurrentYear = @StartYear
WHILE (@NumMonths > 0)
BEGIN
IF @CurrentMonth = 13
BEGIN
SET @CurrentYear = @CurrentYear + 1
SET @CurrentMonth = 1
END
SET @BeginLogicalFileName = CASE @ConcatDatabaseName 
WHEN 1 THEN CONCAT(@DatabaseName, '_', @FGName)
ELSE @FGName
END
SET @FGName = CONCAT(@FileGroupName, @CurrentYear, RIGHT('00' + CONVERT(VARCHAR(2),@CurrentMonth), 2));

SET @SQL = @SQL + REPLACE(REPLACE(REPLACE(
N'
IF NOT EXISTS (
SELECT 1
FROM {{@DatabaseName}}.sys.filegroups
WHERE name = "{{@FGName}}"
)
BEGIN
ALTER DATABASE {{@DatabaseName}} ADD FILEGROUP {{@FGName}};
END
'
,'{{@FGName}}', @FGName)
,'{{@DatabaseName}}', @DatabaseName)
,'"', '''');
SET @NumFiles = @OrgNumFiles
WHILE @NumFiles > 0
BEGIN
SET @LogicalFilename = CONCAT(@BeginLogicalFileName, CASE @Stuff 
WHEN 1 THEN 
CASE LEN(@NumFiles) 
WHEN 1 THEN '_0' + CAST(@NumFiles AS VARCHAR(1))
ELSE '_' + CAST(@NumFiles AS VARCHAR(2))
END
ELSE ''
END)
SET @SQL = @SQL + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
N'
IF NOT EXISTS (
SELECT 1
FROM {{@DatabaseName}}.sys.database_files
WHERE
name = "{{@LogicalFileName}}"
)
BEGIN
ALTER DATABASE {{@DatabaseName}} ADD FILE (
NAME = "{{@LogicalFileName}}",
FILENAME = "{{@PhysicalFilePath}}\{{@LogicalFileName}}.ndf",
SIZE = {{@FilegroupSizeMB}}MB,
FILEGROWTH = {{@FileGrowthMB}}MB
)
TO FILEGROUP {{@FGName}};
END
'
, '{{@LogicalFileName}}', @LogicalFilename)
, '{{@PhysicalFilePath}}', @Directory)
, '{{@FilegroupSizeMB}}', CONVERT(NVARCHAR(8), CASE @Stuff
WHEN 0 THEN @FilegroupSizeMB
ELSE @MaxSizeMB
END))
, '{{@FGName}}', @FGName)
, '{{@FileGrowthMB}}', CONVERT(NVARCHAR(5), @FilegrowthMB))
, '{{@DatabaseName}}', @DatabaseName)
, '"', '''');

SET @NumFiles = @NumFiles - 1
END
SET @NumMonths = @NumMonths - 1;
SET @CurrentMonth = @CurrentMonth + 1
END
END
IF @ByFilegroupName = 1 
BEGIN
SET @FGName = @FileGroupName
SET @BeginLogicalFileName = CASE @ConcatDatabaseName 
WHEN 1 THEN CONCAT(@DatabaseName, '_', @FGName)
ELSE @FGName
END
SET @SQL = REPLACE(REPLACE(REPLACE(
N'
IF NOT EXISTS (
SELECT 1
FROM {{@DatabaseName}}.sys.filegroups
WHERE name = "{{@FGName}}"
)
BEGIN
ALTER DATABASE {{@DatabaseName}} ADD FILEGROUP {{@FGName}};
END
'
,'{{@FGName}}', @FGName)
,'{{@DatabaseName}}', @DatabaseName)
,'"', '''');
SET @NumFiles = @OrgNumFiles
WHILE @NumFiles > 0
BEGIN
SET @LogicalFilename = CONCAT(@BeginLogicalFileName, CASE @Stuff 
WHEN 1 THEN 
CASE LEN(@NumFiles) 
WHEN 1 THEN '_0' + CAST(@NumFiles AS VARCHAR(1))
ELSE '_' + CAST(@NumFiles AS VARCHAR(2))
END
ELSE ''
END)
SET @SQL = @SQL + REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
N'
IF NOT EXISTS (
SELECT 1
FROM {{@DatabaseName}}.sys.database_files
WHERE
name = "{{@LogicalFileName}}"
)
BEGIN
ALTER DATABASE {{@DatabaseName}} ADD FILE (
NAME = "{{@LogicalFileName}}",
FILENAME = "{{@PhysicalFilePath}}\{{@LogicalFileName}}.ndf",
SIZE = {{@FilegroupSizeMB}}MB,
FILEGROWTH = {{@FileGrowthMB}}MB
)
TO FILEGROUP {{@FGName}};
END
'
, '{{@LogicalFileName}}', @LogicalFilename)
, '{{@PhysicalFilePath}}', @Directory)
, '{{@FilegroupSizeMB}}', CONVERT(NVARCHAR(8), CASE @Stuff
WHEN 0 THEN @FilegroupSizeMB
ELSE @MaxSizeMB
END))
, '{{@FGName}}', @FGName)
, '{{@FileGrowthMB}}', CONVERT(NVARCHAR(5), @FilegrowthMB))
, '{{@DatabaseName}}', @DatabaseName)
, '"', '''');

SET @NumFiles = @NumFiles - 1
END
END
IF @Debug = 1
BEGIN
SELECT 'EXEC master.sys.xp_create_subdir @Directory;'
SELECT @SQL FOR XML PATH('');
END
ELSE
EXEC(@SQL);
GO

Here are the six different ways you can run this procedure to files and filegroups for your database:

  1. Set up partitions with a set size for each file
  2. Set up partitions based on the size of a table
  3. By monthly date range with a set size for each file
  4. By monthly date range with sized based on the size of a table
  5. One file with a set size
  6. One file based on the size of a table

Scenario 1: Set up partitions with a set size for each file

EXEC dbo.FileFileGroup_Builder
    @Directory = 'E:\Data',
    @Size = 100,
    @DatabaseName = 'DatabaseName',
    @ByPartitionNumber = 1,
    @NumPartitions = 10,
    @FileGroupName ='FilegroupName',
    @Debug  = 1 -- Change to 0 to execute

Scenario 2: Set up partitions based on the size of a table

EXEC dbo.FileFileGroup_Builder
    @Directory = 'E:\Data',
    @TableName = 'TableName',
    @SchemaName = 'SchemaName',
    @SizeBasedOnTable = 1,
    @DatabaseName = 'DatabaseName',
    @ByPartitionNumber = 1,
    @NumPartitions  = 10,
    @FileGroupName  ='FilegroupName',
    @Debug  = 1 -- Change to 0 to execute

Example output of scenarios #1 and #2:

IF NOT EXISTS (
    SELECT 1
    FROM sys.filegroups
    WHERE name = 'Testing02'
)
BEGIN
    ALTER DATABASE Database ADD FILEGROUP Testing02;
END
 
IF NOT EXISTS (
    SELECT 1
    FROM sys.database_files
    WHERE
        name = 'Testing02'
)
BEGIN
    ALTER DATABASE Database ADD FILE (
        NAME = 'Testing02',
        FILENAME = 'E:\Data\Testing02.ndf',
        SIZE = 100MB,
        FILEGROWTH = 2048MB
    )
    TO FILEGROUP Testing02;
END
 
IF NOT EXISTS (
    SELECT 1
    FROM sys.filegroups
    WHERE name = 'Testing01'
)
BEGIN
    ALTER DATABASE Database ADD FILEGROUP Testing01;
END
 
IF NOT EXISTS (
    SELECT 1
    FROM sys.database_files
    WHERE
        name = 'Testing01'
)
BEGIN
    ALTER DATABASE Database ADD FILE (
        NAME = 'Testing01',
        FILENAME = 'E:\Data\Testing01.ndf',
        SIZE = 100MB,
        FILEGROWTH = 2048MB
    )
    TO FILEGROUP Testing01;
END

Scenario 3: By monthly date range with a set size for each file

EXEC dbo.FileFileGroup_Builder
    @Directory = 'E:\Data',
    @Size = 100,
    @DatabaseName = 'DatabaseName',
    @ByDate = 1,
    @StartDate = 'YYYYMM',
    @EndDate = 'YYYYMM',
    @FileGroupName  ='FilegroupName',
    @Debug  = 1 -- Change to 0 to execute

Scenario 4: By monthly date range with sized based on the size of a table

EXEC dbo.FileFileGroup_Builder
    @Directory = 'E:\Data',
    @TableName = 'TableName',
    @SchemaName = 'SchemaName',
    @SizeBasedOnTable = 1,
    @DatabaseName = 'DatabaseName',
    @ByDate = 1,
    @StartDate = 'YYYYMM',
    @EndDate = 'YYYYMM',
    @FileGroupName  ='FilegroupName',
    @Debug  = 1 -- Change to 0 to execute

Example output for scenarios #3 and #4:

IF NOT EXISTS (
    SELECT 1
    FROM sys.filegroups
    WHERE name = 'Testing201507'
)
BEGIN
    ALTER DATABASE Database ADD FILEGROUP Testing201507;
END
 
IF NOT EXISTS (
    SELECT 1
    FROM sys.database_files
    WHERE
        name = 'Testing201507'
)
BEGIN
    ALTER DATABASE Database ADD FILE (
        NAME = 'Testing201507',
        FILENAME = 'E:\Data\Testing201507.ndf',
        SIZE = 100MB,
        FILEGROWTH = 2048MB
    )
    TO FILEGROUP Testing201507;
END
 
IF NOT EXISTS (
    SELECT 1
    FROM sys.filegroups
    WHERE name = 'Testing201508'
)
BEGIN
    ALTER DATABASE Database ADD FILEGROUP Testing201508;
END
 
IF NOT EXISTS (
    SELECT 1
    FROM sys.database_files
    WHERE
        name = 'Testing201508'
)
BEGIN
    ALTER DATABASE Database ADD FILE (
        NAME = 'Testing201508',
        FILENAME = 'E:\Data\Testing201508.ndf',
        SIZE = 100MB,
        FILEGROWTH = 2048MB
    )
    TO FILEGROUP Testing201508;
END

Scenario 5: One file with a set size

EXEC dbo.FileFileGroup_Builder
    @Directory = 'E:\Data',
    @Size = 100,
    @DatabaseName = 'DatabaseName',
    @ByFilegroupName = 1,
    @FileGroupName  ='FilegroupName',
    @Debug  = 1 -- Change to 0 to execute

Scenario 6: One file based on the size of a table

EXEC DBMaint.FileFileGroup_Builder
    @Directory = 'E:\Data',
    @TableName = 'TableName',
    @SchemaName = 'SchemaName',
    @SizeBasedOnTable = 1,
    @DatabaseName = 'DatabaseName',
    @ByFilegroupName = 1,
    @FileGroupName  ='FilegroupName',
    @Debug  = 1 -- Change to 0 to execute

Example output of Scenarios #5 and #6

IF NOT EXISTS (
    SELECT 1
    FROM sys.filegroups
    WHERE name = 'Testing'
)
BEGIN
    ALTER DATABASE Database ADD FILEGROUP Testing;
END
 
IF NOT EXISTS (
    SELECT 1
    FROM sys.database_files
    WHERE
        name = 'Testing'
)
BEGIN
    ALTER DATABASE Database ADD FILE (
        NAME = 'Testing',
        FILENAME = 'E:\Data\Testing.ndf',
        SIZE = 100MB,
        FILEGROWTH = 2048MB
    )
    TO FILEGROUP Testing;
END

Next

What other scenarios do you have for creating file and filegroups for your databases that could be added?

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

Technical Article

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-23

1,567 reads

Technical Article

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-17

1,530 reads

Technical Article

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

2009-02-13

360 reads