 |
|
 | Files (three types:) |
Primary data files: (.mdf) | Secondary data files: (.ndf) | Log files: (.ldf) |
|---|---|---|
| Mandatory. Is the database "starting point". Holds pointers to other files in the database. Contain schema (structure) and database properties information Contains startup information for the database catalog. May (but should not) also contain objects and user data. Keep only catalog objects in the primary file: reduce disk contention. | These are all data files, other then the primary. Optional and user-defined. Contains objects and user data. Can (should) be distributed across disks/devices to improve access performance. A database may have up to 32,766 secondary data files. | AT LEAST one log file is needed for each database. Used for recovery. Default setting: logs in the same disk in which datafiles are created. Should be stored in separate disks, unless the database is read-only (no contention, then). |
When does the DB Engine uses file location info from primary file ( .mdf)?
|
- The locations of all the files in a database are recorded in the primary file AND in the
master database. - Most of the time, SQLServer DB Engine uses info from the
master database. - File Logical name: used to refer to the physical files in all Transact-SQL stmts.
- File Physical (OS) name:
- NTFS File system recommended.
- Each instance has its own default directory.
- Shared files for ALL INSTANCES are located at:
disk:\Program Files\Microsoft SQL Server\100\ | Data file pages |
|  |
| Filegroups |
Filegroup is a logical structure: database objects can be allocated to filegroups, and can be splitted across various filegroups.
Note: SQL Server x Oracle
|
Primary filegroups:
- Contains the primary datafile + any secondary datafile not allocated to a filegroup.
- All system tables are allocated to the primary filegroup.
- Primary filegroup equivalent to the Oracle SYSTEM TABLESPACE.
Secondary (user-defined) filegroup:
- User-defined.
- A databaes may have up to 32,766 secondary filegroups.
- Created by using FILEGROUP in a CREATE DATABASE or ALTER DATABASE
- Log files are never part of a filegroup.
- Each file belongs to ONLY ONE filegroup.
- If a filegroup has more than one data file, SQL Server distributes the data across the various files.
- SQL Server keeps the amount of data in each file proportional to the file size.
Filegroup properties:
- Can be set or changed from SSMS or using ALTER DATABASE statement
- Default filegroup: One filegroup is designated the DEFAULT filegroup.
- Members of the db_owner database role can switch the default filegroup.
- A filegroup can be made READ-ONLY
- Tables (and other objects) are stored in filegroups, not in files.
 | Create database MyDB (1) Define primary filegroup (logical name, physical name, file size, growth policy) (2) Define secondary filegroup |
USE MASTER; GO -- create db with default data filegroup and one log file. -- specify growth increment and max fize for primary data file. CREATE DATABASE MyDB ON PRIMARY ( name = 'MyDB_primary', filename = 'c:\Program Files\Micros...\MSSQL...\MSSQL\data\MyDB_Prm.mdf', size=4mb, maxsize=10mb, filegrowth=1mb), filegroup MyDB_fg1 ( name = 'MyDB_FG1_Dat1', filename = 'c:\Program Files\Micros...\MSSQL...\MSSQL\data\MyDB_FG1_1.ndf', size=1mb, maxsize=10mb, filegrowth=1mb), ( name = 'MyDB_FG1_Dat2', filename = 'c:\Program Files\Micros...\MSSQL...\MSSQL\data\MyDB_FG1_2.ndf', SIZE = 1MB, MAXSIZE=10MB, FILEGROWTH=1MB) LOG ON ( name='MyDB_archlog1', filename = 'c:\Program Files\Micros...\MSSQL...\MSSQL\data\MyDB_archlog1.ldf', size=1mb, maxsize=10mb, filegrowth=1mb); GO -- NOTE: In a production database, the transaction logs SHOULD be stored on a physical disk OTHER THAN -- the one in which the data files are located.
(3) Alter database define default filegroup.
-- define the default filegroup ALTER DATABASE MyDB modify filegroup MyDB_FG1 DEFAULT; GO
(4) Add a new data file to a filegroup
ALTER DATABASE MyDB ADD FILE ( name = 'MyDB_FG1_Dat3', filename = 'c:\Program Files\Micros...\MSSQL...\MSSQL\data\MyDB_FG1_3.ndf', size=1mb, maxsize=10mb, filegrowth=1mb) TO filegroup MyDB_FG1;
(5) Create table EMP on MyDB
(6) Check table information in the database
-- create table in the user-defined filegroup -- Table EMP created in the dbo schema. USE MyDB; CREATE TABLE emp ( empid INT PRIMARY KEY, name CHAR(20)) ON MyDB_FG1; GO EXEC sp_columns @table_name='emp', @table_owner='dbo' GO
(7) Create a new filegroup
-- Create a new filegroup ALTER DATABASE MyDB ADD filegroup MyDB_FG2; ALTER DATABASE MyDB ADD FILE ( name = 'MyDB_FG2_Dat1', filename = 'c:\Program Files\Micros...\MSSQL...\MSSQL\data\MyDB_FG2_1.ndf', size=1mb, maxsize=10mb, filegrowth=1mb) TO filegroup MyDB_FG2;;
sys.databases |
(a) Information on databases:
sys.databasesUSE MyDB; GO SELECT name, create_date, user_access_desc, state_desc, recovery_model_desc FROM sys.databases;name create_date user_access_desc state_desc recovery_model_desc ------- ---------------------- ---------------- ---------- -------------- master 2003-04-08 09:13:36.390 MULTI_USER ONLINE SIMPLE tempdb 2011-10-06 14:56:52.290 MULTI_USER ONLINE SIMPLE model 2003-04-08 09:13:36.390 MULTI_USER ONLINE FULL msdb 2010-04-02 17:35:08.970 MULTI_USER ONLINE SIMPLE TESTSQL 2011-10-08 10:43:13.347 MULTI_USER ONLINE FULL MyDB 2011-10-08 19:12:38.427 MULTI_USER ONLINE FULL
(b) Information on filegroups:
sys.filegroupsUSE MyDB; GO SELECT * FROM sys.filegroups;name data_space_id type type_desc is_default filegroup_guid log_filegroup_id is_read_only ---------- ------------- ---- ---------------- ---------- ------------------------------------ ---------------- ------------ PRIMARY 1 FG ROWS_FILEGROUP 0 NULL NULL 0 MyDB_fg1 2 FG ROWS_FILEGROUP 1 803DD155-A6A1-4CB5-B183-3FB16D561B13 NULL 0 MyDB_FG2 3 FG ROWS_FILEGROUP 0 D167E2CC-DBB6-45B2-8197-2FF1EA827940 NULL 0 (3 row(s) affected)
(c) Information on data files:
sys.database_filesUSE MyDB; GO SELECT name, type_desc, physical_name, state_desc, size FROM sys.database_files;name type_desc physical_name state_desc size ------------- --------- ------------------------------------------------------------------ ---------- ------ MyDB_primary ROWS c:\Program Files\Micros...\MSSQL10...\MSSQL\data\MyDB_Prm.mdf ONLINE 512 MyDB_archlog1 LOG c:\Program Files\Micros...\MSSQL10...\MSSQL\data\MyDB_archlog1.ldf ONLINE 128 MyDB_FG1_Dat1 ROWS c:\Program Files\Micros...\MSSQL10...\MSSQL\data\MyDB_FG1_1.ndf ONLINE 128 MyDB_FG1_Dat2 ROWS c:\Program Files\Micros...\MSSQL10...\MSSQL\data\MyDB_FG1_2.ndf ONLINE 128 MyDB_FG1_Dat3 ROWS c:\Program Files\Micros...\MSSQL10...\MSSQL\data\MyDB_FG1_3.ndf ONLINE 128 MyDB_FG2_Dat1 ROWS c:\Program Files\Micros...\MSSQL10...\MSSQL\data\MyDB_FG2_1.ndf ONLINE 128
(d) To list the names of the files of all databases in a server:
sys.master_filesSELECT DB_NAME(database_id) AS 'db_name', name AS 'logical filename', physical_name, type_desc FROM sys.master_files;db_name logical filename physical_name type_desc ---------- ---------------- ------------------------------------------------------- ---------- master master C:\Progr...\MSSQL10...\MSSQL\DATA\master.mdf ROWS master mastlog C:\Progr...\MSSQL10...\MSSQL\DATA\mastlog.ldf LOG tempdb tempdev C:\Progr...\MSSQL10...\MSSQL\DATA\tempdb.mdf ROWS tempdb templog C:\Progr...\MSSQL10...\MSSQL\DATA\templog.ldf LOG model modeldev C:\Progr...\MSSQL10...\MSSQL\DATA\model.mdf ROWS model modellog C:\Progr...\MSSQL10...\MSSQL\DATA\modellog.ldf LOG msdb MSDBData C:\Progr...\MSSQL10...\MSSQL\DATA\MSDBData.mdf ROWS msdb MSDBLog C:\Progr...\MSSQL10...\MSSQL\DATA\MSDBLog.ldf LOG TESTSQL2008 TESTSQL2008 C:\Progr...\MSSQL10...\MSSQL\DATA\InsideTSQL2008.mdf ROWS TESTSQL2008 TESTSQL2008_log C:\Progr...\MSSQL10...\MSSQL\DATA\InsideTSQL2008_log.LDF LOG MyDB MyDB_primary c:\Progr...\MSSQL10...\MSSQL\data\MyDB_Prm.mdf ROWS MyDB MyDB_archlog1 c:\Progr...\MSSQL10...\MSSQL\data\MyDB_archlog1.ldf LOG MyDB MyDB_FG1_Dat1 c:\Progr...\MSSQL10...\MSSQL\data\MyDB_FG1_1.ndf ROWS MyDB MyDB_FG1_Dat2 c:\Progr...\MSSQL10...\MSSQL\data\MyDB_FG1_2.ndf ROWS MyDB MyDB_FG1_Dat3 c:\Progr...\MSSQL10...\MSSQL\data\MyDB_FG1_3.ndf ROWS
(e) check data spaces:
USE MyDB; SELECT name, type_desc FROM sys.data_spaces;name type_desc -------- -------------- PRIMARY ROWS_FILEGROUP MyDB_fg1 ROWS_FILEGROUP MyDB_FG2 ROWS_FILEGROUP
