人人都是 DBA（V）SQL Server 資料庫檔案

標籤：des   style   blog   http   io   ar   color   os   使用   

SQL Server 資料庫安裝後會包含 4 個預設系統資料庫：master, model, msdb, tempdb。

SELECT [name]    ,database_id    ,suser_sname(owner_sid) AS [owner]    ,create_date    ,user_access_desc    ,state_descFROM sys.databasesWHERE 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》由 Dennis Gao 發表自部落格園個人技術部落格，未經作者本人同意禁止任何形式的轉載，任何自動或人為的爬蟲轉載或抄襲行為均為耍流氓。

人人都是 DBA（V）SQL Server 資料庫檔案