Adding or Removing tempDB data files (for single or multiple data files in other words .mdf files).
A few days ago I was working on tempDB contentions and how to resolve them. In this article I will explain in depth 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.
use tempDB
go
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:
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev2, FILENAME = 'W:\tempdb2.mdf', SIZE = 256);
And run:
use tempDB
go
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.
USE tempdb;
GO
DBCC SHRINKFILE('tempdev2', EMPTYFILE)
GO
USE master;
GO
ALTER DATABASE tempdb
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:
ALTER DATABASE [tempdb] MODIFY FILE (
NAME = N'tempdev2',
SIZE = 1024KB );
Now once again restart the SQL Server Instances and then execute the following query:
USE tempdb;
GO
DBCC SHRINKFILE('tempdev2', EMPTYFILE)
GO
USE master;
GO
ALTER DATABASE tempdb
REMOVE FILE tempdev2;
This will definitely remove the tempdev2 data file.