人人都是 DBA（V）SQL Server 数据库文件

SQL Server 数据库安装后会包含 4 个默认系统数据库：master, model, msdb, tempdb。

SELECT [name]
    ,database_id
    ,suser_sname(owner_sid) AS [owner]
    ,create_date
    ,user_access_desc
    ,state_desc
FROM sys.databases
WHERE database_id <= 4;

master

master 数据库包含用于记录整个服务器安装信息和后续创建的所有数据库的信息，包括磁盘空间信息、文件分配信息、文件使用信息、系统级的配置项信息、网络终结点信息、用户账户信息、各数据库的信息等。

model

model 数据库是一个模板数据库。每次创建新的数据库时，SQL Server 都会生成 model 数据库的一个副本作为新数据库的基础结构。所以，如果想在创建新的数据库时就拥有某些指定对象、权限和属性设置等，可以更改 model 数据库中的内容，新的数据库会自动继承这些设置。

msdb

msdb 数据库由 SQL Server 提供的一些功能服务组件所使用。包括：

SQL Server Agent：用于执行计划任务，例如备份和复制任务等。
Service Broker：用于提供队列和可靠性消息机制。
Jobs
Alerts
Log Shipping
Policies
Database Mail
Damaged Pages Recovery

tempdb

tempdb 数据库是 SQL Server 的工作空间，其特别之处是当 SQL Server 重启时总是重建而不是恢复该数据库，所以存放的数据在数据库重启后会丢失。tempdb 数据库用于存放由用户显式创建的临时表（Temporary Tables）、查询处理过程的中间数据、排序的中间数据、用于 Snapshot 的行版本数据、游标相关数据等。所有用户都有创建和使用 tempdb 中本地和全局临时表的权利，也就是 # 和 ## 为前缀的临时表。

tempdb 最有可能是在生产环境中创建和删除新对象数量最多的数据库，所以优化对 tempdb 的影响比在用户数据库上的影响更大。由于每个 SQL Server 实例只有一个 tempdb，所以有问题的应用程序会影响到所有其他应用程序中的所有其他用户。

mssqlsystemresource

SQL Server 其实还隐藏着第 5 个神秘系统数据库 mssqlsystemresource，称为系统资源数据库。顾名思义，mssqlsystemresource 数据库中存放的都是系统资源相关的信息，系统内的可执行对象都放在这里，比如 sys.objects$ 对象，后缀为 "$" 的对象在其他数据库中都不可见。这个数据库无法通过 SQL Server Management Studio 直接查看，也无法直接访问，实际上权限的控制导致也无法去修改它。mssqlsystemresource 数据库的主要作用是用于系统升级和补丁安装，以便快速的替换系统内资源定义。

mssqlsystemresource 数据库文件默认存放在 binn 目录下，文件名为 mssqlsystemresource.mdf，同时还存在一个相应的日志文件 mssqlsystemresource.ldf。如果想探究 mssqlsystemresource 数据库的内容，可以拷贝这两个文件到新的目录，重命名文件，重启 SQL Server，然后将重命名后的文件 Attach 为新的数据库。

CREATE DATABASE dennis_resource_copy ON (
    NAME = data
    ,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DENNIS\MSSQL\DATA\mssqlsystemresource_copy.mdf'
    )
    ,(
    NAME = log
    ,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DENNIS\MSSQL\DATA\mssqlsystemresource_copy.ldf'
    )
FOR ATTACH;

这样，SQL Server 对待上面的 dennis_resource_copy 数据库和其他常规数据库没有差别，在 dennis_resource_copy 数据库中修改对象也不会影响 mssqlsystemresource 数据库。

数据库文件

数据库文件实际上和普通的文件系统文件没有什么不同。SQL Server 中允许有 3 中类型的数据库文件：

Primary Data Files：每个数据库都有一个主数据文件，使用 .mdf 扩展名。
Secondary Data Files：数据库可以没有或者有多个辅助数据文件，使用 .ndf 扩展名。
Log Files：每个数据库至少有一个日志文件，使用 .ldf 扩展名。

实际上，针对特殊功能，还存在 FileStream Data Files 和 Full-Text Data Files。

在创建数据库文件时，每个文件都有 5 个属性可以指定：

Logical FileName：逻辑文件名
Physical FileName：物理文件名
Initial Size：初始大小
Maximum Size：最大大小
Growth Increment：增长增量

CREATE DATABASE DENNIS_TEST ON PRIMARY (
    NAME = DENNIS_TEST_PRIMARY
    ,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DENNIS\MSSQL\DATA\DENNIS_TEST_PRIMARY.mdf'
    ,SIZE = 100 MB
    ,MAXSIZE = 200 MB
    ,FILEGROWTH = 20 MB
    )
    ,(
    NAME = DENNIS_TEST_SECONDARY
    ,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DENNIS\MSSQL\DATA\DENNIS_TEST_SECONDARY.ndf'
    ,SIZE = 10 GB
    ,MAXSIZE = 50 GB
    ,FILEGROWTH = 250 MB
    ) LOG ON (
    NAME = DENNIS_TEST_LOG
    ,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DENNIS\MSSQL\DATA\DENNIS_TEST_LOG.ldf'
    ,SIZE = 2 GB
    ,MAXSIZE = 10 GB
    ,FILEGROWTH = 100 MB
    );

可以使用元数据视图 sys.database_files 查看这些属性。

SELECT * FROM sys.database_files;

数据文件的默认大小是 model 数据库的主数据文件的大小（默认 2M），日志文件的默认大小为 0.5M。

出于分配和管理的目的，可以将数据库的数据文件分为文件组。在某些情况下，可以把数据和索引文件放在特定的文件组、特定的驱动器上以提高性能。

包含主数据文件的文件组称为 Primary Filegroup，并且只会存在一个 Primary Filegroup。如果创建数据库时没有特别说明要把文件放在哪个文件组中，则默认会放到 Primary Filegroup 中。当然，也可以修改默认文件组。

CREATE DATABASE DENNIS_TEST ON PRIMARY (
    NAME = DENNIS_Primary1
    ,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DENNIS\MSSQL\DATA\DENNIS_Primary1.mdf'
    ,SIZE = 10
    ,MAXSIZE = 50
    ,FILEGROWTH = 10
    )
    ,(
    NAME = DENNIS_Primary2
    ,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DENNIS\MSSQL\DATA\DENNIS_Primary2.ndf'
    ,SIZE = 10
    ,MAXSIZE = 50
    ,FILEGROWTH = 10
    )
    ,FILEGROUP DENNIS_Group1 (
    NAME = DENNIS_Grp1Fi1e1
    ,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DENNIS\MSSQL\DATA\DENNIS_Grp1Fi1e1.ndf'
    ,SIZE = 5
    ,MAXSIZE = 30
    ,FILEGROWTH = 5
    )
    ,(
    NAME = DENNIS_Grp1Fi1e2
    ,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DENNIS\MSSQL\DATA\DENNIS_Grp1Fi1e2.ndf'
    ,SIZE = 5
    ,MAXSIZE = 30
    ,FILEGROWTH = 5
    )
    ,FILEGROUP DENNIS_Group2 (
    NAME = DENNIS_Grp2Fi1e1
    ,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DENNIS\MSSQL\DATA\DENNIS_Grp2Fi1e1.ndf'
    ,SIZE = 10
    ,MAXSIZE = 50
    ,FILEGROWTH = 5
    )
    ,(
    NAME = DENNIS_Grp2Fi1e2
    ,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DENNIS\MSSQL\DATA\DENNIS_Grp2Fi1e2.ndf'
    ,SIZE = 10
    ,MAXSIZE = 50
    ,FILEGROWTH = 5
    ) LOG ON (
    NAME = DENNIS_log
    ,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.DENNIS\MSSQL\DATA\DENNIS_log.ldf'
    ,SIZE = 5 MB
    ,MAXSIZE = 25 MB
    ,FILEGROWTH = 5 MB
    );

数据库配置选项

状态选项（State options）

SINGLE_USER | RESTRICTED_USER | MULTI_USER
OFFLINE | ONLINE | EMERGENCY
READ_ONLY | READ_WRITE

游标选项（Cursor options）

CURSOR_CLOSE_ON_COMMIT { ON | OFF }
CURSOR_DEFAULT { LOCAL | GLOBAL }

自动选项（Auto options）

AUTO_CLOSE { ON | OFF }
AUTO_CREATE_STATISTICS { ON | OFF }
AUTO_SHRINK { ON | OFF }
AUTO_UPDATE_STATISTICS { ON | OFF }
AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF }

参数化选项（Parameterization options）

DATE_CORRELATION_OPTIMIZATION { ON | OFF }
PARAMETERIZATION { SIMPLE | FORCED }

SQL 选项（SQL options）

ANSI_NULL_DEFAULT { ON | OFF }
ANSI_NULLS { ON | OFF }
ANSI_PADDING { ON | OFF }
ANSI_WARNINGS { ON | OFF }
ARITHABORT { ON | OFF }
CONCAT_NULL_YIELDS_NULL { ON | OFF }
NUMERIC_ROUNDABORT { ON | OFF }
QUOTED_IDENTIFIER { ON | OFF }
RECURSIVE_TRIGGERS { ON | OFF }

数据库恢复选项（Database recovery options）

RECOVERY { FULL | BULK_LOGGED | SIMPLE }
TORN_PAGE_DETECTION { ON | OFF }
PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }

外部访问选项（External access options）

DB_CHAINING { ON | OFF }
TRUSTWORTHY { ON | OFF }

数据库镜像选项（Database mirroring options）

PARTNER { = 'partner_server' }
| FAILOVER
| FORCE_SERVICE_ALLOW_DATA_LOSS
| OFF
| RESUME
| SAFETY { FULL | OFF }
| SUSPEND
| TIMEOUT integer
}
WITNESS { = 'witness_server' }| OFF }

Service Broker 选项（Service Broker options）

ENABLE_BROKER | DISABLE_BROKER
NEW_BROKER
ERROR_BROKER_CONVERSATIONS

更改跟踪选项（Change Tracking options）

CHANGE_TRACKING {= ON [ <change_tracking_settings> | = OFF}

数据库加密选项（Database Encryption options）

ENCRYPTION {ON | OFF}

快照隔离选项（Snapshot Isolation options）

ALLOW_SNAPSHOT_ISOLATION {ON | OFF }
READ_COMMITTED_SNAPSHOT {ON | OFF } [ WITH <termination> ]

可以使用 sys.databases 元数据视图来查看各数据库的选项配置。

SELECT * FROM sys.databases;

可以使用 ALTER DATABASE 命名来修改数据库配置选项。

ALTER DATABASE DENNIS_TEST SET SINGLE_USER;
ALTER DATABASE DENNIS_TEST SET OFFLINE;
ALTER DATABASE DENNIS_TEST SET READ_ONLY; 
ALTER DATABASE DENNIS_TEST SET SINGLE_USER WITH NO_WAIT;

《人人都是 DBA》系列文章索引：

序号	名称
1	人人都是 DBA（I）SQL Server 体系结构
2	人人都是 DBA（II）SQL Server 元数据
3	人人都是 DBA（III）SQL Server 调度器
4	人人都是 DBA（IV）SQL Server 内存管理
5	人人都是 DBA（V）SQL Server 数据库文件
6	人人都是 DBA（VI）SQL Server 事务日志
7	人人都是 DBA（VII）B 树和 B+ 树
8	人人都是 DBA（VIII）SQL Server 页存储结构
9	人人都是 DBA（IX）服务器信息收集脚本汇编
10	人人都是 DBA（X）资源信息收集脚本汇编
11	人人都是 DBA（XI）I/O 信息收集脚本汇编
12	人人都是 DBA（XII）查询信息收集脚本汇编
13	人人都是 DBA（XIII）索引信息收集脚本汇编
14	人人都是 DBA（XIV）存储过程信息收集脚本汇编
15	人人都是 DBA（XV）锁信息收集脚本汇编

本系列文章《人人都是 DBA》由 Dennis Gao 发表自博客园个人技术博客，未经作者本人同意禁止任何形式的转载，任何自动或人为的爬虫转载或抄袭行为均为耍流氓。

posted @ 2014-12-02 08:47 sangmado 阅读(5791) 评论(14) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

{ "熵码匠艺": "Software Craftsmanship" }

人人都是 DBA（V）SQL Server 数据库文件

数据库文件

数据库配置选项

公告