To Modify .ndf (secondary data) Files from the Tempdb database in SQL Server.

SQL Tempdev creates multiple .ndf  files and keeps increasing that cause the drive C: became full.

Resolution: Modify the the NDF files and assign fix file sizes.

Important Considerations

• You cannot remove a tempdb file while SQL Server is running.

• File changes to tempdb must be made via ALTER DATABASE, and SQL Server must be restarted for the changes to take effect.

---

✅ Step-by-step Process to Modify .ndf Files from tempdb

1. Identify the .ndf Files

Run this query to list all tempdb files:

SQL:
USE tempdb;
GO
EXEC sp_helpfile;

This will list .mdf, .ldf, and any additional .ndf files.

---

2. Alter the tempdb to Modify the .ndf Files

You can modify each file using this command:

sql:

USE master;
GO
ALTER DATABASE tempdb MODIFY FILE tempdev2;
-- Replace tempdev2 with the logical name of the .ndf file

Repeat for each .ndf file.

Note: If the file is in use, this command will fail. Since tempdb is re-created every time SQL Server restarts, this command prepares the configuration for the next restart.

---

3. Restart SQL Server

After successfully executing the ALTER DATABASE command(s), restart the SQL Server instance for the changes to take effect.

• You can do this through SQL Server Configuration Manager or Services.msc.

---

4. Verify Removal

After restart, check again:

sql:

USE tempdb;
GO
EXEC sp_helpfile;

The .ndf files should change the file size.

-----------------------------------------------------------------------------------------------------------

To reconfigure tempdb for a 10-core SQL Server using 6 evenly sized data files (tempdev, tempdb2–tempdb6), all placed in the same default directory.

USE master;

GO

-- Primary data file (tempdev)

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, SIZE = 20480MB, MAXSIZE = 20480MB, FILEGROWTH = 0);

-- Secondary data files (temp2 to temp8)

ALTER DATABASE tempdb

MODIFY FILE (NAME = temp2, SIZE = 20480MB, MAXSIZE = 20480MB, FILEGROWTH = 0);

ALTER DATABASE tempdb

MODIFY FILE (NAME = temp3, SIZE = 20480MB, MAXSIZE = 20480MB, FILEGROWTH = 0);

ALTER DATABASE tempdb

MODIFY FILE (NAME = temp4, SIZE = 20480MB, MAXSIZE = 20480MB, FILEGROWTH = 0);

ALTER DATABASE tempdb

MODIFY FILE (NAME = temp5, SIZE = 20480MB, MAXSIZE = 20480MB, FILEGROWTH = 0);

ALTER DATABASE tempdb

MODIFY FILE (NAME = temp6, SIZE = 20480MB, MAXSIZE = 20480MB, FILEGROWTH = 0);

ALTER DATABASE tempdb

MODIFY FILE (NAME = temp7, SIZE = 20480MB, MAXSIZE = 20480MB, FILEGROWTH = 0);

ALTER DATABASE tempdb

MODIFY FILE (NAME = temp8, SIZE = 20480MB, MAXSIZE = 20480MB, FILEGROWTH = 0);

-- Log file (templog) — can be different if needed, but we’ll also fix it to 20 GB

ALTER DATABASE tempdb

MODIFY FILE (NAME = templog, SIZE = 20480MB, MAXSIZE = 20480MB, FILEGROWTH = 0);