Moving tempdb files in SQL Server is a simple operation but requires some planning and a restart of the SQL Server database engine.
When planning it’s important to understand the resource usage of tempdb within your environment. Also, the tempdb database responds well to being on fast storage, like flash, and should have a number of pre-sized data row files equal to the number of cores in the SQL Server instance or eight, whichever is the smaller number.
When Moving tempdb we issue the ALTER DATABASE command as below;
USE [master] ALTER DATABASE [tempdb] MODIFY FILE (NAME= tempdev, FILENAME = 'C:\tempdb\tempdb.mdf') ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME= 'C:\tempdb\tempdb.ldf')
If the location doesn’t exist you’ll get an error:
When the statements successfully execute we’ll get a message back indicaiting the database server needs restarted for the tempdb files to be moved:
When moving tempdb files you can also specify the file size and growth so you can, for example, pre-size your files if they haven’t been already:
USE [master] ALTER DATABASE [tempdb] MODIFY FILE (NAME= tempdev, FILENAME = 'C:\tempdb\tempdb.mdf', SIZE = 4194304KB , MAXSIZE = UNLIMITED, FILEGROWTH = 2097152KB) ALTER DATABASE [tempdb] MODIFY FILE (NAME = templog, FILENAME= 'C:\tempdb\tempdb.ldf', SIZE = 4194304KB , MAXSIZE = UNLIMITED, FILEGROWTH = 2097152KB)
The examples shown above only involve one data row (.mdf) file. In a production environment you would have, at a minimum, four cores and therefore you should have at least four data row files, the initial .mdf file and three .ndf files. Only one log file (.ldf) is required. You can add extra files using the T-SQL ALTER DATABASE ADD FILE command or alternatively using SQL Server Management Studio under database, properties and the files selection.
More general information on tempdb can be found at https://docs.microsoft.com/en-us/sql/relational-databases/databases/tempdb-database and how to add files using ALTER DATABASE can be found at https://technet.microsoft.com/en-us/library/bb522469(v=sql.105).aspx
Thanks for reading!