SQL Server Move Tempdb mdf and ldf
2022-07-12
筆記 SQL Server 如何修改 tempdb mdf 及 ldf 數量以及位置。
MDF
執行指令後，需要進行 SQL Server 伺服器重新啟動。
tempdb 最好有獨立的 Drive，且是速度最快的 Drive，例如伺服器上的 SSD，該 Drive 的所有容量均分給所有的 tempdb mdf 檔案，不用開自動成長的設定方式。
檔案的大小由 Drive 決定，而 Drive 則依照資料庫的效能需求，32 GB 分給 8 個 tempdb mdf ，平均每個 tempdb 4 GB。
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\tempdb.mdf',
SIZE = 512000KB , FILEGROWTH = 102400KB);
GO
ALTER DATABASE [tempdb]
ADD FILE ( NAME = N'temp2', FILENAME = N'E:\tempdb_mssql_2.ndf',
SIZE = 512000KB , FILEGROWTH = 102400KB )
GO
ALTER DATABASE [tempdb]
ADD FILE ( NAME = N'temp3', FILENAME = N'E:\tempdb_mssql_3.ndf',
SIZE = 512000KB , FILEGROWTH = 102400KB )
GO
ALTER DATABASE [tempdb]
ADD FILE ( NAME = N'temp4', FILENAME = N'E:\tempdb_mssql_4.ndf',
SIZE = 512000KB , FILEGROWTH = 102400KB )
GO
ALTER DATABASE [tempdb]
ADD FILE ( NAME = N'temp5', FILENAME = N'E:\tempdb_mssql_5.ndf',
SIZE = 512000KB , FILEGROWTH = 102400KB )
GO
ALTER DATABASE [tempdb]
ADD FILE ( NAME = N'temp6', FILENAME = N'E:\tempdb_mssql_6.ndf',
SIZE = 512000KB , FILEGROWTH = 102400KB )
GO
ALTER DATABASE [tempdb]
ADD FILE ( NAME = N'temp7', FILENAME = N'E:\tempdb_mssql_7.ndf',
SIZE = 512000KB , FILEGROWTH = 102400KB )
GO
ALTER DATABASE [tempdb]
ADD FILE ( NAME = N'temp8', FILENAME = N'E:\tempdb_mssql_8.ndf',
SIZE = 512000KB , FILEGROWTH = 102400KB )
GO其中 FILE 包含 Modify 與 ADD 的處理方式，檔案的 SIZE 與 FILEGROWTH 也可以使用 GB、MB或者是KB 的方式指令。
要注意的是設定完成後要重新啟動 SQL Server 修改才會生效，此外設定的新路徑也必須要允許 NT Service\MSSQLSERVER具有安全性上完全控制的權限，或者是對應的服務使用帳號號必須設定權限。
LDF
ALTER DATABASE [tempdb]
Modify FILE ( NAME = N'templog', FILENAME = N'G:\templog.ldf',
SIZE = 512000KB , FILEGROWTH = 102400KB )
GOldf 最好是配置在獨立的且速度最快的磁碟上，以解決其扮演一切作業可能發生瓶頸的問題。
刪除 tempdb File
如果要移除 tempdb 的 mdf, ndf 或者 ldf 但直接使用 GUI 沒有成功的話，可以先關閉 SQL Server 服務，在啟動參數加入 -f 並且在重新啟動服務。
啟動後再使用下列指令進行移除：
USE master
ALTER DATABASE [tempdb] REMOVE FILE 'temp8'如果要確認要刪除的檔案 name 可以使用 sys.master_files 來查詢。
執行磁碟區維護工作
配合 tempdb 的預設空間增加，使用 Windows Run secpol.msc 依序選擇「本機原則」後選擇「使用者權限指派」，並將 SQL Server Service 帳戶 NT Service\MSSQLSERVICE 授予 執行磁碟區維護工作權限。
