- SQL Server maps a database over a set of operating-system files.
- Data and log information are never mixed in the same file, and individual files are used only by one database.
- Filegroups are named collections of files and are used to help with data placement and administrative tasks such as backup and restore operations.
---------------------------------------- Database Files ----------------------------------------------
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 is .mdf.
Secondary data files
- Secondary data files make up all the data
files, other than the primary data file.
- Some databases may not have any
secondary data files, while others have several secondary data files.
- The
recommended file name extension for secondary data files is .ndf.
Log files
- Log files hold all the log information that is 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.
--------------------------------Logical and Physical File Names----------------------------
SQL Server files have two names:
logical_file_name
- The logical_file_name is the name used to refer to the
physical file in all Transact-SQL statements.
- The logical file name must comply
with the rules for SQL Server identifiers and must be unique among logical file
names in the database.
os_file_name
- The os_file_name is the name of the physical file including
the directory path.
- It must follow the rules for the operating system file
names.
SQL Server data and log files can be put on either FAT or NTFS file systems. We
recommend using the NTFS file system because the security aspects of NTFS.
Read/write data filegroups and log files cannot be placed on an NTFS compressed
file system. Only read-only databases and read-only secondary filegroups can be
put on an NTFS compressed file system.
--------------------------------------------- File Size ------------------------------------------------
- SQL Server files can grow automatically from their originally
specified size.
- When you define a file, you can specify a specific growth
increment. Every time the file is filled, it increases its size by the growth
increment. If there are multiple files in a filegroup, they will not autogrow
until all the files are full. Growth then occurs in a round-robin fashion.
- Each file can also have a maximum size specified. If a maximum size
is not specified, the file can continue to grow until it has used all available
space on the disk.
Database Snapshot Files
The form of file that is used by a database snapshot to store its
copy-on-write data depends on whether the snapshot is created by a user or used
internally:
- A database snapshot that is created by a user stores its data in one or more
sparse files. Sparse file technology is a feature of the NTFS file system. At
first, a sparse file contains no user data, and disk space for user data has not
been allocated to the sparse file.
- Database snapshots are used internally by certain DBCC commands. These commands include DBCC CHECKDB, DBCC CHECKTABLE, DBCC CHECKALLOC, and DBCC CHECKFILEGROUP. An internal database snapshot uses sparse alternate data streams of the original database files. Like sparse files, alternate data streams are a feature of the NTFS file system. The use of sparse alternate data streams allows for multiple data allocations to be associated with a single file or folder without affecting the file size or volume statistics.
------------------------------------ Database Filegroups -----------------------------------------
Database objects and files can be grouped together in filegroups for
allocation and administration purposes. 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
- User-defined filegroups are any filegroups that are specified by
using the FILEGROUP keyword in a CREATE DATABASE or ALTER DATABASE statement.
- Log files are never part of a filegroup. Log space is managed
separately from data space.
- No file can be a member of more than one filegroup.
- Tables, indexes,
and large object data can be associated with a specified filegroup. In this
case, all their pages will be allocated in that filegroup, or the tables and
indexes can be partitioned. The data of partitioned tables and indexes is
divided into units each of which can be placed in a separate filegroup in a
database.
- One filegroup in each database is designated the default filegroup.
When a table or index is created without specifying a filegroup, it is assumed
all pages will be allocated from the default filegroup.
- Only one filegroup at a
time can be the default filegroup.
- Members of the db_owner fixed database role can switch the
default filegroup from one filegroup to another. If no default filegroup is
specified, the primary filegroup is the default filegroup.
File and Filegroup Example
The following example creates a database on an instance of SQL
Server. The database has a primary data file, a user-defined filegroup, and a
log file. The primary data file is in the primary filegroup and the user-defined
filegroup has two secondary data files. An ALTER DATABASE statement makes the
user-defined filegroup the default. A table is then created specifying the
user-defined filegroup.
USE master;
GO
-- Create the database with the default data
-- filegroup and a log file. Specify the
-- growth increment and the max size for the
-- primary data file.
CREATE DATABASE MyDB
ON PRIMARY
( NAME='MyDB_Primary',
FILENAME=
'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB_Prm.mdf',
SIZE=4MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
FILEGROUP MyDB_FG1
( NAME = 'MyDB_FG1_Dat1',
FILENAME =
'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB_FG1_1.ndf',
SIZE = 1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB),
( NAME = 'MyDB_FG1_Dat2',
FILENAME =
'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB_FG1_2.ndf',
SIZE = 1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB)
LOG ON
( NAME='MyDB_log',
FILENAME =
'c:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\data\MyDB.ldf',
SIZE=1MB,
MAXSIZE=10MB,
FILEGROWTH=1MB);
GO
ALTER DATABASE MyDB
MODIFY FILEGROUP MyDB_FG1 DEFAULT;
GO
-- Create a table in the user-defined filegroup.
USE MyDB;
CREATE TABLE MyTable
( cola int PRIMARY KEY,
colb char(8) )
ON MyDB_FG1;
GO
No comments:
Post a Comment