搜尋本站文章

2018-07-05

[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




沒有留言:

張貼留言