德瑞克：SQL Server 學習筆記: 即時回收 FILESTREAM 不再使用的基礎檔案，使用 CHECKPOINT，以 SQL Server 2008 為例

刪除 FILESTREAM 資料

當您刪除包含 FILESTREAM 欄位的資料列時，您也會刪除其基礎檔案系統的檔案。
因此，刪除資料列與檔案的唯一方法，是使用 Transact-SQL DELETE 陳述式。

雖然系統已經不再使用這些相關聯的檔案，但這些檔案可能不是即時執行回收作業。

依據文件的說明：

「基礎檔案(underlying file)」會由 FILESTREAM 記憶體回收行程所移除。
The underlying files are removed by the FILESTREAM garbage collector.

應該是使用「記憶體回收器(garbage collector，GC)」來執行回收作業。

-- 01_FileSteam_使用的基礎檔案

可能作法如下

-- 執行 CHECKPOINT 來即時回收 FILESTREAM 不再使用的基礎檔案
/*
將目前資料庫的所有「中途分頁(Dirty Page)」 寫入磁碟中。
「中途分頁」是已進入緩衝區快取中，也已修改過，但尚未寫入磁碟的資料頁面。
*/
USE [目標資料庫];
GO
CHECKPOINT;
GO

FILESTREAM 概觀

FILESTREAM 會將 SQL Server Database Engine 與 NTFS 檔案系統整合，其方式是將 varbinary(max) 二進位大型物件 (BLOB) 資料當做檔案儲存在檔案系統上。

Transact-SQL 陳述式可以插入、更新、查詢、搜尋及備份 FILESTREAM 資料。

Win32 檔案系統介面提供了資料的資料流方式存取。

FILESTREAM 會使用 NT 系統快取來儲存檔案資料。

如此可減少 FILESTREAM 資料可能對 Database Engine 效能產生的任何影響。

並不會使用 SQL Server 緩衝集區；因此，此記憶體可用於查詢處理。

即使啟用了透明資料加密，FILESTREAM 資料也不會加密。

--
FILESTREAM 儲存體

FILESTREAM 儲存體會實作為 varbinary(max) 資料行，該資料行中的資料會當做 BLOB 儲存在檔案系統上。

BLOB 的大小只受到檔案系統磁碟區大小的限制。

標準 varbinary(max) 限制 (2-GB 檔案大小) 不適用於檔案系統中所儲存的 BLOB。

若要指定資料行應該將資料儲存在檔案系統上，請在 varbinary(max) 資料行上指定 FILESTREAM 屬性。

如此會讓 Database Engine 將該資料行的所有資料都儲存在檔案系統上，而不是儲存在資料庫檔案中。

FILESTREAM 資料必須儲存在 FILESTREAM 檔案群組中。

FILESTREAM 檔案群組是包含檔案系統目錄 (而非檔案本身) 的特殊檔案群組，這些檔案系統目錄稱為「資料容器」(Data Container)。

資料容器是 Database Engine 儲存體與檔案系統儲存體之間的介面。

認識 CHECKPOINT (Transact-SQL)

將目前資料庫的所有「中途分頁(Dirty Page)」寫入磁碟中。
「中途分頁」是已進入緩衝區快取中，也已修改過，但尚未寫入磁碟的資料頁面。

藉由建立一個點來確保所有中途分頁都已寫入磁碟中，檢查點可讓稍後的復原節省時間。

語法：

CHECKPOINT [ checkpoint_duration ]

引數：
checkpoint_duration
指定所要求的檢查點作業完成的時間 (以秒為單位)。
當指定 checkpoint_duration 時，SQL Server Database Engine 會嘗試在要求的持續時間內執行檢查點。

checkpoint_duration 必須是 int 類型的運算式，且必須大於零。
當省略這個參數時，SQL Server Database Engine 會自動調整檢查點持續時間，使資料庫應用程式受到的效能影響降到最低。

在 SQL Server 2000 中，檢查點處理序的時間範圍會以 sp_configure RECOVERY INTERVAL 設定為基礎。

--
基於效能的考量，Database Engine 會修改記憶體中的資料庫頁面，但並不會在每次變更之後，將頁面寫入磁碟中。

不過，Database Engine 必須定期執行檢查點，將這些中途分頁寫入磁碟中。

將中途分頁寫入磁碟中，會建立一個已知的恰當起點，使 Database Engine 在發生非預期的關機或損毀之後，能夠從這裡開始套用復原期間包含在記錄中的變更。

檢查點可以同時出現在任意數目的資料庫中。

Database Engine 無法從中斷的檢查點進行復原。
如果檢查點中斷，且需要復原，Database Engine 就必須從先前執行成功的檢查點開始復原。

--
引起檢查點的事件

在資料庫備份之前，Database Engine 會自動執行檢查點，使備份能夠包含資料庫頁面的所有變更。

另外，當發生下列中的任何情況時，也會自動執行檢查點：

1. 記錄之使用中的部份，超出伺服器在 recovery interval 伺服器組態選項所指定的時間中，所能復原的大小。

2. 記錄已填滿 70%，資料庫處於記錄截斷模式中。

當下面這兩個條件都是 TRUE 時，資料庫就會進入記錄截斷模式：
資料庫在使用簡單復原模式，以及在執行參考資料庫的 BACKUP DATABASE 陳述式之後，發生下列事件之一：

2.1 在資料庫中執行記錄最少的作業，例如，執行記錄最少的大量複製作業，或記錄最少的 WRITETEXT 陳述式。

2.2 執行在資料庫中新增或刪除檔案的 ALTER DATABASE 陳述式。

--
另外，停止伺服器也會在伺服器的每個資料庫中發出檢查點。

下列停止 SQL Server 的方法會執行每個資料庫的檢查點作業：

1. 使用 SQL Server 組態管理員。
2. 使用 SQL Server Management Studio。

3. 使用 SHUTDOWN 陳述式。

SHUTDOWN WITH NOWAIT 陳述式會關閉 SQL Server，但不會在每個資料庫中執行檢查點作業。
這可能造成後來重新啟動時，在正常時間之外，花更多時間來復原伺服器中的資料庫。

4. 在 [命令提示字元] 視窗中，使用 net stop mssqlserver 命令。
5. 使用 [控制台] 中的 [服務]，選取 [mssqlserver]，再按一下 [停止]。

6. 讓叢集中的執行個體離線。

--
影響檢查點作業持續時間的因素

一般而言，檢查點作業必須寫入的中途分頁數愈多，檢查點作業所需要的時間也會愈長。

為了儘量避免影響其他應用程式的效能，依預設，SQL Server 會調整檢查點作業所執行的寫入頻率。

在自動檢查點及未指定 checkpoint_duration 值的 CHECKPOINT 陳述式上，SQL Server 都會使用這個策略。

降低寫入頻率會增加完成檢查點作業所需要的時間。

您可以利用 checkpoint_duration 來要求檢查點作業在特定時間之內完成。

checkpoint_duration 對效能的影響，會隨著中途分頁數、系統活動數及所指定的實際持續時間而不同。

例如，如果檢查點通常會在 120 秒之內完成，將 checkpoint_duration 指定為 45 秒，會使 SQL Server 用超出預設指派的資源數量來執行檢查點作業。

相對地，將 checkpoint_duration 指定為 180 秒，會使 SQL Server 指派低於預設指派的資源數量。

一般而言，checkpoint_duration 愈短，檢查點作業所花的資源愈多，checkpoint_duration 愈長，檢查點作業所花的資源愈少。

可能的話，SQL Server 一律會完成檢查點作業，檢查點作業完成時，CHECKPOINT 陳述式會立即傳回。

因此，在某些情況下，檢查點作業的完成會比指定的持續時間快，執行時間也有可能超出指定的持續時間。

--
權限

CHECKPOINT 權限預設會授與系統管理員 (sysadmin) 固定伺服器角色以及 db_owner 和 db_backupoperator 固定資料庫角色的成員，這些權限不能轉讓。