Tag: file groups

SQL Server : File Groups

File groups are similar to Oracle tablespaces. They are a way to group files and separate
load. This allows you to spread storage across a variety of LUNs and perform backup/recovery at file group level.

  • Datafiles: Equal fill out algorithm. All files in a file group are equally full.
  • Logfiles: Logs fill up one at a time. As a result, can’t use file groups.

Create a new file group.

USE master
GO

ALTER DATABASE MyDatabase ADD FILEGROUP MyFileGroup
GO

Add a new datafile to an existing file group.

ALTER DATABASE MyDatabase
ADD FILE (NAME = N'MyNewDatafile',
          FILENAME = N'e:\db\MyNewDatafile.ndf',
          SIZE = 10MB,
          FILEGROWTH = 1GB)
TO FILEGROUP MyFileGroup
GO

Display the datafiles and file groups.

USE MyDatabase
GO

SELECT * from sys.database_files
SELECT * FROM sys.data_spaces

SELECT ds.name, df.name, df.physical_name
FROM sys.database_files df
 JOIN sys.data_spaces ds ON ds.data_space_id = df.data_space_id

SELECT * FROM sys.master_files

Create a table in the file group.

CREATE TABLE employees2
(
 employee_id INT IDENTITY(1,1) PRIMARY KEY,
 first_name VARCHAR(30),
 middle_names VARCHAR(100),
 last_name VARCHAR(30),
 date_of_birth DATE
)
ON MyFileGroup
GO

For more information see: