How to Find Database Growth using T-SQL

In this post, I am going to share a stored procedure to calculate the percentage of file growth of a database. This growth rate can be handy to plan ahead for future storage needs.


IF OBJECT_ID(N'dbo.DatabaseGrowth', 'P') IS NOT NULL
BEGIN
    PRINT 'Dropping procedure dbo.DatabaseGrowth'
    DROP PROCEDURE dbo.DatabaseGrowth
END
GO

PRINT 'Creating procedure dbo.DatabaseGrowth'
GO

CREATE PROC dbo.DatabaseGrowth @pDBName sysname = NULL
AS
BEGIN
/*********************************************************************************
Description: Procedure to calulate the file growth %ages for a given database and
show the growth rate so that we can plan ahead for future storage needs.

How to use:
--------------
Example 1: To see the file growth of the current database:
EXEC dbo.DatabaseGrowth

Example 2: To see the file growth for [Test] database:
EXEC dbo.DatabaseGrowth 'Test'

--------------------------------------------------------------------------------
Change History
--------------------------------------------------------------------------------
Date Name Comments
---------- ----------- ---------------------------------------------------------
01/15/2012 Hari Sharma Created
********************************************************************************/
SET NOCOUNT ON;
DECLARE @DatabaseName SYSNAME

-- Use current database, if a database name is not specified in input parameter
SET @DatabaseName = ISNULL(@pDBName, DB_NAME())
SELECT  backup_start_date AS StartTime
        ,@DatabaseName AS DatabaseName
        ,filegroup_name AS FilegroupName
        ,logical_name AS LogicalFilename
        ,physical_name AS PhysicalFilename
        ,CONVERT(NUMERIC(9,2), file_size/1048576) AS FileSizeInMB
        ,Growth AS PercentageGrowth
FROM (
     SELECT b.backup_start_date
           ,a.backup_set_id
           ,a.file_size
           ,a.logical_name
           ,a.[filegroup_name]
           ,a.physical_name
           ,(SELECT CONVERT(numeric(5,2),((a.file_size * 100.00)/i1.file_size)-100)
             FROM msdb.dbo.backupfile i1
             WHERE i1.backup_set_id =
              (
              SELECT MAX(i2.backup_set_id)
              FROM msdb.dbo.backupfile i2 JOIN msdb.dbo.backupset i3
              ON i2.backup_set_id = i3.backup_set_id
              WHERE i2.backup_set_id < a.backup_set_id
              AND i2.file_type='D'
              AND i3.database_name = @DatabaseName
              AND i2.logical_name = a.logical_name
              AND i2.logical_name = i1.logical_name
              AND i3.type = 'D'
              )
              AND i1.file_type = 'D'
) AS Growth
FROM msdb.dbo.backupfile a
JOIN msdb.dbo.backupset b
ON a.backup_set_id = b.backup_set_id
WHERE b.database_name = @DatabaseName
AND a.file_type = 'D'
AND b.type = 'D'
) AS Derived
WHERE   ISNULL(Growth, 0.0) <> 0.0
ORDER BY logical_name, StartTime
END

GO

 

How To Add File Group and New File to an Existing Database

In this post I am going to explain following questions:
1. How to add a File Group to a Database.
2. How to remove a File Group from a Database
3. How to add a File to an existing File Group.
4. How to remove a File from a File Group.

How to add a File Group to a Database
I am assuming that I have a Test database with default File Group (Primary). Now I want to add one more File Group with Secondary name. We can use following command to add new File Group:

USE MASTER
GO
IF NOT EXISTS(
SELECT name FROM Test.sys.filegroups
WHERE name = 'Seconday')
BEGIN
PRINT 'Adding [Seconday] FileGroup...'
ALTER DATABASE TestADD FileGroup Seconday
END


How to remove a File Group from a Database
You can use following command to drop existing File Group:

USE MASTER
GO
IF EXISTS(
SELECT name FROM Test.sys.filegroups
WHERE name = 'Seconday'
)
BEGIN
PRINT 'Dropping [Seconday] FileGroup...'
ALTER DATABASE Test
REMOVE FileGroup Seconday
END

How to add a File to an existing File Group
Lets assume that we want to add Data1 file (.ndf) to Secondary file group which we created in step1. We can use following command add new file:

IF NOT EXISTS(
SELECT name FROM Test.sys.database_files
WHERE name = 'Data1'
)
BEGIN
PRINT 'Adding [Data1] file to [Seconday] File Group...'
ALTER DATABASE Test
ADD FILE
(
NAME = Data1,
FILENAME = 'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Test1.ndf',
SIZE = 200MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 5%
)
TO FILEGROUP Seconday
END
GO


How to remove a File from a File Group
We can use following command to remove the data file [data1] from [Secondary] file group  using following command:

IF EXISTS(
SELECT * FROM Test.sys.database_files
WHERE name = 'Data1'
)
BEGIN
PRINT 'Removing [Data1] file from [Seconday] File Group...'
ALTER DATABASE Test
REMOVE File Data1
END