T-SQL 資料庫建立與檢視：檔案群組、自動成長與 sp_helpdb 實戰

前言

生產環境資料庫建立遠不止 CREATE DATABASE MyDB 這麼簡單。合理規劃檔案群組、初始大小、自動成長增量，能避免未來效能調校的無數個夜晚。本文實戰演示多檔案群組建庫腳本與檢視技巧。

一、生產級 CREATE DATABASE 完整範本

IF DB_ID('MyDatabase6') IS NOT NULL
    DROP DATABASE MyDatabase6;
GO

CREATE DATABASE MyDatabase6
ON PRIMARY
(   -- 主資料檔
    NAME = 'LogicNameOfDataFile6_1',
    FILENAME = 'D:\SQLData\DataFile6_1.mdf',
    SIZE = 500MB,
    MAXSIZE = 100GB,
    FILEGROWTH = 256MB
),
FILEGROUP UserFG6_2
(   -- 次要資料檔 1
    NAME = 'LogicNameOfDataFile6_2',
    FILENAME = 'D:\SQLData\DataFile6_2.ndf',
    SIZE = 200MB,
    MAXSIZE = 50GB,
    FILEGROWTH = 128MB
),
FILEGROUP UserFG6_3
(   -- 次要資料檔 2
    NAME = 'LogicNameOfDataFile6_3',
    FILENAME = 'E:\SQLData\DataFile6_3.ndf',  -- 不同磁碟
    SIZE = 300MB,
    MAXSIZE = 100GB,
    FILEGROWTH = 256MB
)
LOG ON
(   -- 交易記錄檔
    NAME = 'LogicNameOfLogFile6',
    FILENAME = 'L:\SQLLog\LogFile6.ldf',
    SIZE = 1000MB,
    MAXSIZE = 200GB,
    FILEGROWTH = 512MB
);
GO

關鍵參數解析

二、Instant File Initialization (IFI) 必開設定

• 授予 SQL Server 服務帳號 「執行磁碟區維護工作」 權限 (SeManageVolumePrivilege)。
• 效果：資料檔擴充時跳過零填充，**秒級完成 TB 級擴充**；記錄檔不受益（必須零填充）。

三、檢視資料庫結構的三種方式

3.1 系統檢視：目前資料庫檔案細節

SELECT 
    type_desc, name, physical_name,
    size * 8. / 1024 AS Size_MB,
    max_size * 8. / 1024 AS MaxSize_MB,
    growth * 8. / 1024 AS Growth_MB,
    CASE is_percent_growth WHEN 1 THEN '百分比' ELSE 'MB' END AS Growth_Type
FROM sys.database_files;

3.2 系統程序：sp_helpdb 快速概覽

EXEC sp_helpdb 'MyDatabase6';
-- 回傳兩個結果集：資料庫屬性 + 檔案清單

3.3 跨資料庫檢視：sys.master_files

SELECT 
    DB_NAME(database_id) AS DBName,
    type_desc, name, physical_name,
    size * 8. / 1024 AS Size_MB
FROM sys.master_files
WHERE database_id = DB_ID('MyDatabase6');

四、常見維運腳本

4.1 修改自動成長為固定 MB

ALTER DATABASE MyDatabase6
MODIFY FILE (NAME = 'LogicNameOfDataFile6_1', FILEGROWTH = 256MB);

4.2 新增檔案到指定群組

ALTER DATABASE MyDatabase6
ADD FILE (
    NAME = 'DataFile6_4',
    FILENAME = 'D:\SQLData\DataFile6_4.ndf',
    SIZE = 100MB, FILEGROWTH = 128MB
) TO FILEGROUP UserFG6_2;

4.3 變更預設檔案群組

ALTER DATABASE MyDatabase6 MODIFY FILEGROUP UserFG6_2 DEFAULT;
-- 新建表預設放入 UserFG6_2，而非 PRIMARY

五、避坑指南

1. 預設路徑陷阱：未指定 FILENAME 會建在 SQL Server 安裝目錄，生產環境**務必顯式指定路徑**。
2. 記錄檔過多 VLF：初始 SIZE 過小、成長增量過小會產生大量 Virtual Log Files，拖慢啟動/還原/備份。建議初始 1GB+、成長 512MB+。
3. 自動收縮：AUTO_SHRINK OFF（預設關閉），切勿開啟，會造成嚴重碎片與效能抖動。