Once in a while as a DBA you might come across a situation where in you are required to remove a data file from the tempdb configuration of your SQL Server instance.
You might want to think that removing a tempdb data file should be as easy as executing an Alter Database statement as given below, however since the tempdb data files are generally not empty, SQL Server throws back an error message to apprise you of the same.
ALTER DATABASE tempdb REMOVE FILE tempdev2
Next, you might want to try emptying the data file using the DBCC ShrinkFile command and then run the Alter Database statement to remove the required data file.
DBCC SHRINKFILE (‘tempdev2’, EMPTYFILE)
However, in all likeliness this would not succeed either.
The objects inside the tempdb can belong to any of the following 3 different categories –
1. Internal Objects
2. User Objects
3. Version Store
Work tables and work files are a part of the Internal Objects category within the tempdb and by design they cannot be moved within the data file by the algorithm defined within the DBCC ShrinkFile command. This essentially means that the empty file operation fails and returns the error message as indicated in the snapshot above.
Now, here comes the step by step approach that we can follow to fix this issue.
Step 1 – Boot the SQL Server Instance in Minimal Mode (-f)
The first thing that we need to do in this situation is to boot the SQL Server in the minimal mode.
1. Open the Administrator command prompt and type the following command to shut down the SQL Server instance. Note that the name of the service will change depending upon whether it’s a default or a named SQL Server instance.
a. Net Stop MSSQLSERVER
2. Once the SQL Server service has been shut down. We go ahead with the next command to reboot the SQL Server in the minimal mode.
a. Net Start MSSQLSERVER /f
Now the SQL Server is running in the minimal mode. In the minimal mode, SQL Server does not load the actual tempdb configuration and rather creates a new tempdb data and log file at the default path which would have been provided at the time of running the SQL Server setup wizard.
Note: If the default tempdb path does not exist or if the SQL Server service account does not have access to that path, the SQL Server will shut down and will not boot in the minimal mode. To understand where the SQL Server is failing in such a case, open the admin command prompt and traverse to the Binn folder for your SQL Server installation within the Program Files. Once there, execute the command given in the snapshot below.
This method once again tries to boot the SQL Server instance in the minimal mode however this method is way more verbose and can help you identify the point of failure much faster.
3. Once the SQL Server is in the minimal mode, next step is to connect to the SQL Server instance through SQLCMD. The syntax for the command is –
Sqlcmd –S <name of the sql server instance>
You might want to have a look at various other sqlcmd related switches and options at this Microsoft page below –