How do I delete tempdb files in SQL Server?

Question

All tempdb files are re-created during startup. However, they are empty and can be removed. To remove additional files in tempdb, use the ALTER DATABASE command by using the REMOVE FILE option. Use the DBCC SHRINKDATABASE command to shrink the tempdb database.

What should I do if SQL Server tempdb is full?

When investigating a TempDB issue like this, most simply restart the SQL Server instance. It's easy to see why they do – the issue quite often locks up completely, and if a customer wants their server to work again ASAP, then a restart is almost inevitable. A restart will tackle the symptom, but not the cause.

Can I delete tempdb NDF files?

Only when the file is not used it can be removed. You can also just remove the file from the properties of the database. When you restart the SQL Server service the [tempdb] is (re-)created.

Can we delete tempdb MDF?

In the end, yes, it was safe for me to delete this file. SHRINKFILE wasn't working consistently (don't know why, probably something basic), and deleting the file worked perfectly. Kind of a voodoo move, yes, but the file was automatically recreated and no problems occurred.

How many tempdb files should I have in SQL Server?

According to Microsoft Support, the best approach is to create one tempdb data file per logical processor up to 8 data files. If your system has more than 8 logical processors, start with 8 data files and monitor your server's workload to determine if more data files would be beneficial.

Expert · Answer