You may want to delete that extra Tempdb data file which someone created by mistake or to accommodate a query. Whatever may be the reason, today I am going to show you how to do it and what issues you may face.
You can run the below query to remove the data file:
ALTER DATABASE tempdb REMOVE FILE LogicalFileName; GO
Or you can use GUI:
SQL Server throws the following error:
The error message says that it cannot remove the file because it is not empty.
If you just add a file and there is no ongoing activity in the file then it would allow to remove the file this way.
For each file you want to remove, you need to run the following command to empty the file and then run the above query to remove the file:
USE [tempdb];
GO
DBCC SHRINKFILE (LogicalName, EMPTYFILE);
GO
USE [tempdb]; GO DBCC SHRINKFILE (LogicalName, EMPTYFILE); GO
But what if it throws the below error:
The error message is about a work table page which can not be removed. Work table is related to cached information, which indicates that you need to clear the cache.
Execute the below queries one by one and after clearing each, try to empty the file again:
DBCC DROPCLEANBUFFERS
GO
DBCC FREEPROCCACHE
GO
DBCC FREESESSIONCACHE
GO
DBCC FREESYSTEMCACHE ('ALL')
GO
The successful run shows as below:
Please keep in mind that whenever you are clearing cache, it can cause performance issues. I am leaving the decision up to you to handle the risk factor according to the environment.
If your environment allows you to restart SQL Service you can just restart which will empty the tempdb files and you can run the remove command:
ALTER DATABASE tempdb REMOVE FILE LogincalName; GO
I hope you have already debunked the Myth around Remote Query Timeout option.
