Home > SQL Server > Delete a tempdb file

Delete a tempdb file

2013/08/07

A best practice is to create multiple db files for the tempdb, tipically 1 for each processor core.

And another best practice is that tempdb files should not be on the same disk of the data files (.mdf)

Trying to delete a tempdb files after the creation of the new ones in Sql Management Studio can cause an error that the file cannot be deleted because not empty.

The solution is DBCC , for example we should delete a tempdb file named tempdev4 which is the Logical name, the default tempdb has 1 one file named tempdev and under it there are the others files


In this case we have for example an tempdev4 under tempdev that cannot be deleted, using

USE tempdb
GO
DBCC SHRINKFILE 
('tempdev4' , EMPTYFILE)

then

ALTER DATABASE tempdb REMOVE FILE tempdev4

works

Advertisements
Categories: SQL Server
%d bloggers like this: