Add Data Files to SQL Server tempdb Database

If the existing drive where the tempdb system database is located does not have enough space you may want to move tempdb database from one drive to another drive, or add another data file for the tempdb database on another drive.
You may want to add data files to the tempdb database to reduce tempdb storage contention.

First check the current tempdb data file names, location and size:

EXEC sp_helpfile

Result will be similar to this:

tempdev 1 C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb.mdf PRIMARY 524288 KB Unlimited 262144 KB data only
templog 2 C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\templog.ldf NULL 131072 KB Unlimited 10% log only


It is recommended to create additional tempdb data files the same size for the best proportional fill performance.
You don't have to restart the SQL Server after adding files to the tempdb database.

To add a data file using T-SQL:

ALTER DATABASE tempdb ADD FILE ( NAME = N'tempdev2',
FILENAME = N'D:\Data\tempdev2.ndf' , SIZE = 512MB , FILEGROWTH = 256MB)

The second data file is successfully added to the tempdb database:


To add a data file using SQL Server Management Studio:

Right click on the tempdb database in Object Explorer and click 'Properties'.
On the 'Files' tab, click 'Add' button:


Enter properties of new data files and click 'OK' to confirm:


To see how to Remove Secondary Data File from SQL Server Database, read related blog post:
How to Remove Secondary Data File from SQL Server Database