SQL Server 數(shù)據(jù)庫安裝后會包含 4 個默認(rèn)系統(tǒng)數(shù)據(jù)庫：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 數(shù)據(jù)庫包含用于記錄整個服務(wù)器安裝信息和后續(xù)創(chuàng)建的所有數(shù)據(jù)庫的信息，包括磁盤空間信息、文件分配信息、文件使用信息、系統(tǒng)級的配置項(xiàng)信息、網(wǎng)絡(luò)終結(jié)點(diǎn)信息、用戶賬戶信息、各數(shù)據(jù)庫的信息等。
model
model 數(shù)據(jù)庫是一個模板數(shù)據(jù)庫。每次創(chuàng)建新的數(shù)據(jù)庫時，SQL Server 都會生成 model 數(shù)據(jù)庫的一個副本作為新數(shù)據(jù)庫的基礎(chǔ)結(jié)構(gòu)。所以，如果想在創(chuàng)建新的數(shù)據(jù)庫時就擁有某些指定對象、權(quán)限和屬性設(shè)置等，可以更改 model 數(shù)據(jù)庫中的內(nèi)容，新的數(shù)據(jù)庫會自動繼承這些設(shè)置。
msdb
msdb 數(shù)據(jù)庫由 SQL Server 提供的一些功能服務(wù)組件所使用。包括：
- SQL Server Agent：用于執(zhí)行計(jì)劃任務(wù)，例如備份和復(fù)制任務(wù)等。
- Service Broker：用于提供隊(duì)列和可靠性消息機(jī)制。
- Jobs
- Alerts
- Log Shipping
- Policies
- Database Mail
- Damaged Pages Recovery
tempdb
tempdb 數(shù)據(jù)庫是 SQL Server 的工作空間，其特別之處是當(dāng) SQL Server 重啟時總是重建而不是恢復(fù)該數(shù)據(jù)庫，所以存放的數(shù)據(jù)在數(shù)據(jù)庫重啟后會丟失。tempdb 數(shù)據(jù)庫用于存放由用戶顯式創(chuàng)建的臨時表（Temporary Tables）、查詢處理過程的中間數(shù)據(jù)、排序的中間數(shù)據(jù)、用于 Snapshot 的行版本數(shù)據(jù)、游標(biāo)相關(guān)數(shù)據(jù)等。所有用戶都有創(chuàng)建和使用 tempdb 中本地和全局臨時表的權(quán)利，也就是 # 和 ## 為前綴的臨時表。
tempdb 最有可能是在生產(chǎn)環(huán)境中創(chuàng)建和刪除新對象數(shù)量最多的數(shù)據(jù)庫，所以優(yōu)化對 tempdb 的影響比在用戶數(shù)據(jù)庫上的影響更大。由于每個 SQL Server 實(shí)例只有一個 tempdb，所以有問題的應(yīng)用程序會影響到所有其他應(yīng)用程序中的所有其他用戶。
mssqlsystemresource
SQL Server 其實(shí)還隱藏著第 5 個神秘系統(tǒng)數(shù)據(jù)庫?mssqlsystemresource，稱為系統(tǒng)資源數(shù)據(jù)庫。顧名思義，mssqlsystemresource 數(shù)據(jù)庫中存放的都是系統(tǒng)資源相關(guān)的信息，系統(tǒng)內(nèi)的可執(zhí)行對象都放在這里，比如 sys.objects$ 對象，后綴為 "$" 的對象在其他數(shù)據(jù)庫中都不可見。這個數(shù)據(jù)庫無法通過 SQL Server Management Studio 直接查看，也無法直接訪問，實(shí)際上權(quán)限的控制導(dǎo)致也無法去修改它。mssqlsystemresource 數(shù)據(jù)庫的主要作用是用于系統(tǒng)升級和補(bǔ)丁安裝，以便快速的替換系統(tǒng)內(nèi)資源定義。
mssqlsystemresource 數(shù)據(jù)庫文件默認(rèn)存放在 binn 目錄下，文件名為?mssqlsystemresource.mdf，同時還存在一個相應(yīng)的日志文件?mssqlsystemresource.ldf。如果想探究?mssqlsystemresource 數(shù)據(jù)庫的內(nèi)容，可以拷貝這兩個文件到新的目錄，重命名文件，重啟 SQL Server，然后將重命名后的文件 Attach 為新的數(shù)據(jù)庫。
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 數(shù)據(jù)庫和其他常規(guī)數(shù)據(jù)庫沒有差別，在?dennis_resource_copy 數(shù)據(jù)庫中修改對象也不會影響?mssqlsystemresource 數(shù)據(jù)庫。
數(shù)據(jù)庫文件
數(shù)據(jù)庫文件實(shí)際上和普通的文件系統(tǒng)文件沒有什么不同。SQL Server 中允許有 3 中類型的數(shù)據(jù)庫文件：
- Primary Data Files ：每個數(shù)據(jù)庫都有一個主數(shù)據(jù)文件，使用 .mdf 擴(kuò)展名。
- Secondary Data Files ：數(shù)據(jù)庫可以沒有或者有多個輔助數(shù)據(jù)文件，使用 .ndf 擴(kuò)展名。
- Log Files ：每個數(shù)據(jù)庫至少有一個日志文件，使用 .ldf 擴(kuò)展名。
實(shí)際上，針對特殊功能，還存在 FileStream Data Files 和 Full-Text Data Files。
在創(chuàng)建數(shù)據(jù)庫文件時，每個文件都有 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
);
可以使用元數(shù)據(jù)視圖 sys.database_files 查看這些屬性。
SELECT
*
FROM
sys.database_files;
數(shù)據(jù)文件的默認(rèn)大小是 model 數(shù)據(jù)庫的主數(shù)據(jù)文件的大小（默認(rèn) 2M），日志文件的默認(rèn)大小為 0.5M。
出于分配和管理的目的，可以將數(shù)據(jù)庫的數(shù)據(jù)文件分為文件組。在某些情況下，可以把數(shù)據(jù)和索引文件放在特定的文件組、特定的驅(qū)動器上以提高性能。
包含主數(shù)據(jù)文件的文件組稱為 Primary Filegroup，并且只會存在一個 Primary Filegroup。如果創(chuàng)建數(shù)據(jù)庫時沒有特別說明要把文件放在哪個文件組中，則默認(rèn)會放到 Primary Filegroup 中。當(dāng)然，也可以修改默認(rèn)文件組。
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
);
數(shù)據(jù)庫配置選項(xiàng)
狀態(tài)選項(xiàng)（State options）
- SINGLE_USER | RESTRICTED_USER | MULTI_USER
- OFFLINE | ONLINE | EMERGENCY
- READ_ONLY | READ_WRITE
游標(biāo)選項(xiàng)（Cursor options）
- CURSOR_CLOSE_ON_COMMIT { ON | OFF }
- CURSOR_DEFAULT { LOCAL | GLOBAL }
自動選項(xiàng)（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 }
參數(shù)化選項(xiàng)（Parameterization options）
- DATE_CORRELATION_OPTIMIZATION { ON | OFF }
- PARAMETERIZATION { SIMPLE | FORCED }
SQL 選項(xiàng)（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 }
數(shù)據(jù)庫恢復(fù)選項(xiàng)（Database recovery options）
- RECOVERY { FULL | BULK_LOGGED | SIMPLE }
- TORN_PAGE_DETECTION { ON | OFF }
- PAGE_VERIFY { CHECKSUM | TORN_PAGE_DETECTION | NONE }
外部訪問選項(xiàng)（External access options）
- DB_CHAINING { ON | OFF }
- TRUSTWORTHY { ON | OFF }
數(shù)據(jù)庫鏡像選項(xiàng)（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 選項(xiàng)（Service Broker options）
- ENABLE_BROKER | DISABLE_BROKER
- NEW_BROKER
- ERROR_BROKER_CONVERSATIONS
更改跟蹤選項(xiàng)（Change Tracking options）
- CHANGE_TRACKING {= ON [ <change_tracking_settings> | = OFF}
數(shù)據(jù)庫加密選項(xiàng)（Database Encryption options）
- ENCRYPTION {ON | OFF}
快照隔離選項(xiàng)（Snapshot Isolation options）
- ALLOW_SNAPSHOT_ISOLATION {ON | OFF }
- READ_COMMITTED_SNAPSHOT {ON | OFF } [ WITH <termination> ]
可以使用 sys.databases 元數(shù)據(jù)視圖來查看各數(shù)據(jù)庫的選項(xiàng)配置。
SELECT
*
FROM
sys.databases;
可以使用 ALTER DATABASE 命名來修改數(shù)據(jù)庫配置選項(xiàng)。
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 ?發(fā)表自 博客園 個人技術(shù)博客，未經(jīng)作者本人同意禁止任何形式的轉(zhuǎn)載，任何自動或人為的爬蟲轉(zhuǎn)載或抄襲行為均為耍流氓。
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長非常感激您！手機(jī)微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
