I explained how to change the disks of a normal database in the article “How To Change The Disk Of Database Files On SQL Server“.
You cannot move tempdb with the same method.
Because the tempdb database is re-created by the sql server each time the SQL Server service starts.
Therefore, with the help of the following script, you can configure tempdb files to be created on the corresponding disks when the service is starrted again.
First of all, you should edit the script as I explained in the article.
|
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 |
use master go alter database tempdb modify file (name = tempdev, filename = 'H:\Disk01\Tempdb\tempdb.mdf') go alter database tempdb modify file (name = tempdev10, filename = 'H:\Disk01\Tempdb\tempdev10.ndf') go alter database tempdb modify file (name = tempdev11, filename = 'H:\Disk01\Tempdb\tempdev11.ndf') go alter database tempdb modify file (name = tempdev12, filename = 'H:\Disk01\Tempdb\tempdev12.ndf') go alter database tempdb modify file (name = tempdev13, filename = 'H:\Disk01\Tempdb\tempdev13.ndf') go alter database tempdb modify file (name = tempdev14, filename = 'H:\Disk01\Tempdb\tempdev14.ndf') go alter database tempdb modify file (name = tempdev2, filename = 'H:\Disk01\Tempdb\tempdev2.ndf') go alter database tempdb modify file (name = tempdev3, filename = 'H:\Disk01\Tempdb\tempdev3.ndf') go alter database tempdb modify file (name = tempdev4, filename = 'H:\Disk01\Tempdb\tempdev4.ndf') go alter database tempdb modify file (name = tempdev5, filename = 'H:\Disk01\Tempdb\tempdev5.ndf') go alter database tempdb modify file (name = tempdev6, filename = 'H:\Disk01\Tempdb\tempdev6.ndf') go alter database tempdb modify file (name = tempdev7, filename = 'H:\Disk01\Tempdb\tempdev7.ndf') go alter database tempdb modify file (name = tempdev8, filename = 'H:\Disk01\Tempdb\tempdev8.ndf') go alter database tempdb modify file (name = tempdev9, filename = 'H:\Disk01\Tempdb\tempdev9.ndf') go alter database tempdb modify file (name = tempdev15, filename = 'H:\Disk01\Tempdb\tempdev15.ndf') go alter database tempdb modify file (name = tempdev16, filename = 'H:\Disk01\Tempdb\tempdev16.ndf') go alter database tempdb modify file (name = templog, filename = 'H:\Disk01\Tempdb\templog.ldf') |
In the above script, we configure 16 files of the tempdb database to be created on the disk named Disk01 mounted under the H drive.
You can find the concept of mount in my article “What is Mounted Drive and How to Create It“.
You will need to edit the above script according to your tempdb file number and the names of your files.
You can see the number and names of Tempdb files with the help of the following script.
|
1 |
select * from sys.sysaltfiles where dbid=DB_ID('tempdb') |
