Tempdb Data Files Remove:
Tomorrow I had a change activity scheduled to remove Tempdb Data Files. So, I am testing the same in my local instance .
I have created 3 tempdb ndf files along with 1 Primary Data file and given a size of each to 1024MB. Please find the screenshot below :
From above SS we can see all Data files are having 99% free Space Available. As the change needs to be done on Production environment which will not be in same case like above , I am creating a temporary table and inserting the data into the table for filling up Tempdb . After filling up Tempdb we have 0% free space Available in all Tempdb files , please find SS below :
So, for removing file in a Database we need to first EMPTY the file and then initiate the command for removing the file.
To EMPTY the file we need to use shrink command DBCC SHRINKFILE (YourLogicalFileName, EMPTYFILE);
Here I am trying to remove the Tempdb Datafile ‘tempdev3‘, I executed below command in TEMPDB :
DBCC SHRINKFILE (tempdev3, EMPTYFILE);
After executing the above command it took sometime and returned with error like shown in below SS:
I tried restarting the instance and executed above command but no luck got the same error , I also tried connecting in Single User mode to run above command but still no luck returned with same error.
So, I thought to execute some DBCC commands and run Remove file command by putting it all together in a Same Query window . Please find below SS:
Now the error says the file not EMPTY , So, I tried emptying the file but after that also getting failed.
I have googled about how to remove tempdb files in a Production environment ,seen so many sites and most of them said to take the Instance in a Minimal configuration Mode i.e., start the Instance with /f Parameter , connect using SQLCMD and then try removing the file.
So, I tried about method by Stopping SQL Services on my local using Command Prompt , below is the command I used:
NET STOP MSSQLSERVER
After stopping SQL Services I started my default Instance using /f parameter :
As said above after connecting instance in Minimal Configuration Mode ,Now I am connecting with SQLCMD to my local Instance as shown below but it returns error while connecting due to Named Pipes in DISABLED State :
I ENABLED Named Pipes and restarted the instance to take effect but still no luck getting below error Server is in Single User mode.
I tried stopping instance again and start with /f parameter and then tried connecting using SQLCMD mode.
If you see in above SS its got connected with SQLCMD mode after multiple attempts. So, I directly executed the statement
ALTER DATABASE [tempdb] REMOVE FILE [tempdev3]
And it didn’t returned any error So, I hope the file was removed successfully, Also here I didn’t run the command DBCC SHRINKFILE (tempdev3, EMPTYFILE), I ran Alter database directly to remove file and taken the instance offline ,started instance without /f Parameter . After starting the instance, connected to my default instance and checked tempdb files, it (tempdev3) was removed
But the file tempdev3 is still exist on physical location as shown below :
I am trying to DELETE file on physical location and was removed without any issue, After deleting Space also reclaimed to disk