您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 移動資料後清空並刪除檔案群組——完整腳本與空間驗證

編輯：關於SqlServer

承上篇將資料從 FG_Test_Id_01 移至 FG_Test_Id_02，本文示範如何確認來源群組已空、安全移除檔案與檔案群組，並提供詳細空間使用率查詢腳本。

移除檔案與檔案群組腳本

-- 接上篇環境，資料已移至 FG_Test_Id_02
-- 1. 移除檔案 (必須先於移除檔案群組)
ALTER DATABASE [Test] REMOVE FILE [FG_TestUnique_Id_01_data];
GO

-- 2. 移除檔案群組
ALTER DATABASE [Test] REMOVE FILEGROUP [FG_Test_Id_01];
GO

空間使用率驗證腳本 (通用版，適用任何資料庫)

USE [Test];
GO
SET NOCOUNT ON;

-- 建立暫存表收集 DBCC SHOWFILESTATS 結果
CREATE TABLE #Data (
    FileID INT, FileGroupId INT, TotalExtents INT, UsedExtents INT,
    FileName SYSNAME, FilePath NVARCHAR(MAX), FileGroup VARCHAR(MAX) NULL
);
CREATE TABLE #Results (
    db SYSNAME, FileType VARCHAR(4), FileGroup SYSNAME, FileName SYSNAME,
    TotalMB NUMERIC(18,2), UsedMB NUMERIC(18,2), PctUsed NUMERIC(18,2),
    FilePath NVARCHAR(MAX), FileID INT
);
CREATE TABLE #Log (
    db SYSNAME, LogSize NUMERIC(18,5), LogUsed NUMERIC(18,5), Status INT, FilePath NVARCHAR(MAX)
);

INSERT #Data (FileID, FileGroupId, TotalExtents, UsedExtents, FileName, FilePath)
EXEC ('DBCC SHOWFILESTATS WITH NO_INFOMSGS');

UPDATE #Data SET FileGroup = sysfilegroups.groupname
FROM #Data JOIN sysfilegroups ON #Data.FileGroupId = sysfilegroups.groupid;

INSERT INTO #Results (db, FileGroup, FileType, FileName, TotalMB, UsedMB, PctUsed, FilePath, FileID)
SELECT DB_NAME(), FileGroup, 'Data', FileName,
       TotalExtents * 64.0 / 1024.0,
       UsedExtents * 64.0 / 1024.0,
       UsedExtents * 100.0 / TotalExtents,
       FilePath, FileID
FROM #Data;

INSERT #Log (db, LogSize, LogUsed, Status, FilePath)
EXEC ('DBCC SQLPERF(LOGSPACE) WITH NO_INFOMSGS');

INSERT INTO #Results (db, FileGroup, FileType, FileName, TotalMB, UsedMB, PctUsed, FilePath, FileID)
SELECT DB_NAME(), 'Log', 'Log', s.name,
       s.size / 128.0,
       FILEPROPERTY(s.name, 'SpaceUsed') / 128.0,
       (FILEPROPERTY(s.name, 'SpaceUsed') / 128.0) * 100.0 / (s.size / 128.0),
       s.filename, s.fileid
FROM #Log l JOIN master.dbo.sysaltfiles f ON f.dbid = DB_ID()
             JOIN dbo.sysfiles s ON s.fileid = f.fileid AND (s.status & 0x40) <> 0
WHERE l.db = DB_NAME();

SELECT
    r.db AS [Database],
    r.FileType AS [File Type],
    CASE WHEN r.FileGroup = 'Log' THEN 'N/A' ELSE r.FileGroup END AS [File Group],
    r.FileName AS [Logical File Name],
    r.TotalMB AS [Total Size (MB)],
    r.UsedMB AS [Used (MB)],
    r.PctUsed AS [Used (%)],
    r.FilePath AS [Physical Path],
    r.FileID AS [File ID],
    CASE WHEN s.maxsize = -1 THEN NULL ELSE CONVERT(DECIMAL(18,2), s.maxsize / 128.0) END AS [Max Size (MB)],
    CONVERT(DECIMAL(18,2), s.growth / 128.0) AS [Autogrowth (MB)]
FROM #Results r JOIN dbo.sysfiles s ON r.FileID = s.fileid
ORDER BY 1,2,3,4;

DROP TABLE #Data, #Results, #Log;