A Practical Approach to Managing Database Size

Listing 1: Code That Creates the Database and Adds Two File Groups

--BEGIN CALLOUT A
CREATE DATABASE TestDB

ON PRIMARY

  (NAME = TestDB_dat,

    FILENAME = 'C:\TestDB_PRIMARY_dat.mdf',

    SIZE = 10MB,

    MAXSIZE = 500MB,

    FILEGROWTH = 15%),

--END CALLOUT A
 

--BEGIN CALLOUT B

FILEGROUP DATA1 DEFAULT

  (NAME = TestDB_Data1_dat_01,

    FILENAME = 'C:\TestDB_DATA1_dat_01.ndf',

    SIZE = 10MB,

    MAXSIZE = 500MB,

    FILEGROWTH = 15%),

  (NAME = TestDB_Data1_dat_02,

    FILENAME = 'C:\TestDB_DATA1_dat_02.ndf',

    SIZE = 10MB,

    MAXSIZE = 500MB,

    FILEGROWTH = 15%),

 

FILEGROUP DATA2

  (NAME = TestDB_Data2_dat_01,

    FILENAME = 'C:\TestDB_DATA2_dat_01.ndf',

    SIZE = 512KB,

    MAXSIZE = 520KB,

    FILEGROWTH = 10KB),

  (NAME = TestDB_Data2_dat_02,

    FILENAME = 'C:\TestDB_DATA2_dat_02.ndf',

    SIZE = 512KB,

    MAXSIZE = 520KB,

    FILEGROWTH = 1KB)

--END CALLOUT B

 

LOG ON

  (NAME = TestDB_log,

    FILENAME = 'C:\TestDB_log.ldf',

    SIZE = 5MB,

    MAXSIZE = 25MB,

    FILEGROWTH = 5MB);

Listing 2: Code That Creates and Populates the Tables

USE [TestDB]
GO

 

CREATE TABLE [dbo].[tblData1](

  [Field1] [int] NOT NULL,

  [Field2] [varchar](100) NULL,

  PRIMARY KEY CLUSTERED

  (

    [Field1] ASC

  ) ON [DATA1]

) ON [DATA1];

 

CREATE NONCLUSTERED INDEX IX_tblData1_Field2

  ON [dbo].[tblData1]([Field2]);

 

CREATE TABLE [dbo].[tblData2](

  [Field1] [int] NOT NULL,

  [Field2] [varchar](100) NULL,

  PRIMARY KEY CLUSTERED

  (

    [Field1] ASC

  ) ON [DATA2]

) ON [DATA2];

 

CREATE NONCLUSTERED INDEX IX_tblData2_Field2

  ON [dbo].[tblData2]([Field2]);

GO

 

DECLARE @counter int;

SET @counter = 1

WHILE (@counter < 10000)

BEGIN

  INSERT INTO dbo.tblData1 (Field1, Field2)

    VALUES (@counter, 'Some text ' + CAST(@counter as varchar(20)))

  INSERT INTO dbo.tblData2 (Field1, Field2)

    VALUES (@counter, 'Some text ' + CAST(@counter as varchar(20)))

  SET @counter = @counter + 1;

END

Listing 3: Code That Resizes the Data2 File Group

USE [master]
GO

ALTER DATABASE [TestDB]

MODIFY FILE ( NAME = N'TestDB_Data2_dat_01', SIZE = 10MB );

ALTER DATABASE [TestDB]

MODIFY FILE ( NAME = N'TestDB_Data2_dat_02', SIZE = 10MB );

GO

Listing 4: Code That Adds an Additional File to the Data2 File Group

USE [master]
GO

ALTER DATABASE [TestDB]

ADD FILE ( NAME = N'TestDB_Data2_dat_03'

  , FILENAME = N'C:\TestDB_DATA2_dat_03.ndf'

  , SIZE = 10240KB

  , FILEGROWTH = 1024KB )

TO FILEGROUP [DATA2];

GO