SQL Server – File and FileGroup basics

Posted: June 11, 2008 in SQL

SQL File and FileGroups :
SQL Server databases have three types of files:
• Primary data files
The primary data file is the starting point of the database and points to the other files in the database. Every database has one primary data file. The recommended file name extension for primary data files is .mdf (Microsoft Data Format File).
• Secondary data files
Secondary data files comprise all of the data files other than the primary data file. Some databases may not have any secondary data files, while others have multiple secondary data files. The recommended file name extension for secondary data files is .ndf(NimbleGen Design File).
• Log files
Log files hold all of the log information used to recover the database. There must be at least one log file for each database, although there can be more than one. The recommended file name extension for log files is .ldf(Layered Data Format File). Log files are never a part of a filegroup. Log space is managed separately from data space.

SQL Server data and log files can be placed on either FAT or NTFS file systems, but cannot be placed on compressed file systems.
FileGroups are logical containers used to store database files.
There are two types of filegroups:
• Primary
The primary filegroup contains the primary data file and any other files not specifically assigned to another filegroup. All pages for the system tables are allocated in the primary filegroup.
• User-defined or Secondary
User-defined filegroups are any filegroups specified using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.

Advertisements

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