Often you will need to add or move the tempDB data files, either to match the core count (up to 8 as best practice), or move them to an SSD or Flash card for performance. Below is a template script you can use. Just alter the drive letter, path and size to your standards and run. You will need to recycle the SQL services for the changes to take effect.
NOTE: Make sure you get all files names and paths correct or you will have issues when SQL Server cannot restart.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
-- use this query to monitor the paths USE master; GO -- Verify the current settings SELECT DB_NAME(database_id) AS 'Database Name', name AS 'Logical File Name', physical_name AS 'Physical File Location', state_desc AS 'State' FROM sys.master_files WHERE database_id IN (DB_ID(N'msdb'), DB_ID(N'model'), DB_ID(N'master'), DB_ID(N'tempdb')) ORDER BY DB_NAME(database_id); GO -- The main data file ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'T:\SqlData\tempdb.mdf', SIZE = 8); GO -- the log file ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'T:\SqlData\templog.ldf', SIZE = 8); GO -- The rest of the data files ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'T:\SqlData\tempdb2.mdf', SIZE = 8); GO ALTER DATABASE tempdb ADD FILE (NAME = tempdev3, FILENAME = 'T:\SqlData\tempdb3.mdf', SIZE = 8); GO ALTER DATABASE tempdb ADD FILE (NAME = tempdev4, FILENAME = 'T:\SqlData\tempdb4.mdf', SIZE = 8); GO ALTER DATABASE tempdb ADD FILE (NAME = tempdev5, FILENAME = 'T:\SqlData\tempdb5.mdf', SIZE = 8); GO ALTER DATABASE tempdb ADD FILE (NAME = tempdev6, FILENAME = 'T:\SqlData\tempdb6.mdf', SIZE = 8); GO ALTER DATABASE tempdb ADD FILE (NAME = tempdev7, FILENAME = 'T:\SqlData\tempdb7.mdf', SIZE = 8); GO ALTER DATABASE tempdb ADD FILE (NAME = tempdev8, FILENAME = 'T:\SqlData\tempdb8.mdf', SIZE = 8); GO |
Views – 1860