A tempdb can have multiple data files. Let's learn how to add and remove tempdb data files.

A few days ago, I was working on tempDB contentions and how to resolve them. In this article, I will explain, how to resolve errors encountered when adding and deleting multiple tempDB data files.

To determine how many tempDB files exist, run the following query in SQL Server Management Studio.

  1. use tempDB
  2. go
  3. EXEC SP_HELPFILE;
This will give you the details about data files, log files and ndf files.

To add a data file in atempDB, use the following query:

  1. ALTER DATABASE tempdb
  2. ADD FILE (NAME = tempdev2, FILENAME = 'W:\tempdb2.mdf', SIZE = 256);
And run:
  1. use tempDB
  2. go
  3. EXEC SP_HELPFILE;
You will see 3 files. This way you can add multiple tempDB data files. Now to remove tempDB data files use the following query.

Restart SQL Server Instances and then execute the following query.

  1. USE tempdb;
  2. GO
  3. DBCC SHRINKFILE('tempdev2', EMPTYFILE)
  4. GO
  5. USE master;
  6. GO
  7. ALTER DATABASE tempdb
  8. REMOVE FILE tempdev2;
This will shrink the tempdb2.mdf data file and remove it.

If the error still exists then modify the tempdev2 file by reducing its size to only 1 MB.

To modify a temp db size run the following query:

  1. ALTER DATABASE [tempdb] MODIFY FILE (
  2. NAME = N'tempdev2',
  3. SIZE = 1024KB );
Now once again restart the SQL Server Instances and then execute the following query:
  1. USE tempdb;
  2. GO
  3. DBCC SHRINKFILE('tempdev2', EMPTYFILE)
  4. GO
  5. USE master;
  6. GO
  7. ALTER DATABASE tempdb
  8. REMOVE FILE tempdev2;

This will remove the tempdev2 data file.

In this article, you learned how to add and delete tempdb data files.