[SQL Server] Implement AUTOGROW_ALL_FILES Option: All files in the filegroup grow.

延續前文：[SQL Server] AUTOGROW_ALL_FILES Option: All files in the filegroup grow.

若要啟用 AUTOGROW_ALL_FILES 選項

• 需要安排於 該資料庫 沒有任何連線 時。
• 若資料庫上仍有 connection 連線，將無法啟用 AUTOGROW_ALL_FILES 選項。

---

Implement AUTOGROW_ALL_FILES Option: All files in the filegroup grow.




01. SQL Server 2016 提供資料庫選項： AUTOGROW_ALL_FILES 。

• 預設 tempdb 已啟用 AUTOGROW_ALL_FILES。
• 設定當 FileGroup 中的某個檔案達到自動成長閾值時， FileGroup 中的所有檔案都會自動同時成長。
• 減少 allocation contention(配置競爭) 問題，提升效能。

02. 觀察：全部資料庫的每個 FileGroup 是否有啟用 AUTOGROW_ALL_FILES 選項。

-- figure 31_Get is_autogrow_all_files option in all databases

-- figure 01_Only_Enable AUTOGROW_ALL_FILES option in tempdb

02. 在 SSMS 17.8.1 版本 亦支援 AUTOGROW_ALL_FILES 屬性與產生 T-SQL Script。

• 若有使用者已經連線到該資料庫時，無法變更 AUTOGROW_ALL_FILES 屬性。
• 遭遇到 Error 5070 的錯誤訊息。

Error Message

Msg 5070, Level 16, State 2, Line 30

Database state cannot be changed while other users are using the database 'FG_Move'

------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Database state cannot be changed while other users are using the database 'FG_Move' (Microsoft SQL Server, Error: 5070)

-- figure 11_Database state cannot be changed while other users are using the database 'FG_Move'

-- figure 41_Error_5070_Database state cannot be changed while other users are using the database

03. 使用以下方式來 啟用 資料庫選項：AUTOGROW_ALL_FILES


(1) ALTER DATABASE [DBName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

• 設定資料庫為 SINGLE_USER (單一使用者模式)。
• 該資料庫的全部連線都會立即中斷。

(2) ALTER DATABASE [DBName] MODIFY FILEGROUP [FileGroup] AUTOGROW_ALL_FILES

• 設定 FILEGROUP： G4_ALL_FILES，啟用 AUTOGROW_ALL_FILES。

(3) ALTER DATABASE [DBName] SET MULTI_USER;

• 允許任何具備適當權限的使用者來連接資料庫。

-- figure 21_Enable AUTOGROW_ALL_FILES option

04. 觀察：全部資料庫的每個 FileGroup 是否有啟用 AUTOGROW_ALL_FILES  選項。

• 確認 FG_Move 資料庫，FILEGROUP： G4_ALL_FILES，已經啟用 AUTOGROW_ALL_FILES。

-- figure 22_Get is_autogrow_all_files option in all databases

05. 使用 SSMS 17.8.1 版本 檢查是否有啟用 AUTOGROW_ALL_FILES 屬性。


-- figure 23_Autogrow_All_Files_SSMS

---

Sample Code


20180705_Implement AUTOGROW_ALL_FILES Option
https://drive.google.com/drive/folders/0B9PQZW3M2F40OTk3YjI2NTEtNjUxZS00MjNjLWFkYWItMzg4ZDhhMGIwMTZl?usp=sharing

---

Reference

[SQL Server] AUTOGROW_ALL_FILES Option: All files in the filegroup grow.

http://sharedderrick.blogspot.com/2018/07/sql-server-autogrowallfiles-option-all.html