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》系列文章索引：
本系列文章《 人人都是 DBA 》由? Dennis Gao ?發表自 博客園 個人技術博客，未經作者本人同意禁止任何形式的轉載，任何自動或人為的爬蟲轉載或抄襲行為均為耍流氓。
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
