Database Best Practices with a Default Secondary Filegroup

It is good practice to set up a secondary filegroup on a SQL Server database and make that the default before you create any objects inside the new database.  Just ask Buck Woody ! http://blogs.msdn.com/b/buckwoody/archive/2009/05/21/sql-server-best-practices-setting-a-default-filegroup.aspx.

Here is some sample code to add a secondary filegroup to your brand new database, add two equal sized files to the secondary filegroup and make this filegroup the new default for your database.  100Mb was specified for file growth but remember it is better to presize your data files.


ALTER DATABASE [YourDatabase] ADD FILEGROUP [SECONDARYFG]

GO

ALTER DATABASE [YourDatabase] ADD FILE

(

NAME = secondaryfgdat1,

FILENAME = 'C:\SQLFolder\secondaryfgdat1.ndf',

SIZE = 100MB,

MAXSIZE = 100MB,

FILEGROWTH = 100MB

),

(

NAME = secondaryfgdat2,

FILENAME = 'C:\SQLFolder\secondaryfgdat2.ndf',

SIZE = 100MB,

MAXSIZE = 100MB,

FILEGROWTH = 100MB

)

TO FILEGROUP SECONDARYFG;

GO

ALTER DATABASE [YourDatabase]

MODIFY FILEGROUP SECONDARYFG DEFAULT;

GO

About AussieBICG

Connect with me here https://au.linkedin.com/in/aussiebicg

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s