Moving one TempdB on 3 instance server

Question

I have a large virtual SQL Server (Full 2008R2).

I run 3 SQL Server instances and would like to relocate the TempdB database file to another location, splitting off from the TempdB log file.

The trouble I am having is that, even though the query ALTER DATABASE executes successfully, and then restarting that particular instance's SQL Server Service, does not relocate that database file.

Do I have to restart the whole server to move this database?

Answer 1

Tempdb files are recreated each time the sql server starts. After you change their location using ALTER DATABASE, you can verify your change by checking the dmv tempdb.sys.database_files. (not sys.master_files, which won't be updated until sql restarts).

Sql will create new files for tempdb, and will leave the old ones on disk, so you should delete them.

Answer 2

Below script will help you :

/****************************************************************************************************************************
--Step 1: Open a New Query Window and run the below command to get the path and names of the TempDB.
****************************************************************************************************************************/

USE TempDB
GO
EXEC sp_helpfile
GO

--Or

SELECT name, physical_name FROM sys.master_files 
WHERE database_id = DB_ID('TempDB');

/****************************************************************************************************************************
--Step 2: In the following query Replace NewPath with new drive path and run the command to change the LDF and MDF file path.
****************************************************************************************************************************/

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'drive:\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'drive:\templog.ldf');
GO

--Result : The tempDB definition has been changed, but no changes are made to until you restart SQLServer. Please stop and restart SQL Server 
/****************************************************************************************************************************
--Step 3: Stop and Start the SQLServer
****************************************************************************************************************************/

--Control Panel -- > Administrative Tools --> Services --> Right click on SQLServer (MSSQLSERVER or INSTANCENAME) --> Restart

--or

--SQL Server Configuration Manager --> SQLServer 2008R2 Services --> Right Click on SQLServer (MSSQLSERVER or INSTANCENAME) --> Restart

--Now TempDB files created in new drive.

/****************************************************************************************************************************
--Step 4: Check the TempDB new file location patch 
****************************************************************************************************************************/

SELECT name, physical_name FROM sys.master_files 
WHERE database_id = DB_ID('TempDB');

As a side note: Do refer to below post from Paul Randall about sizing tempdb :

On all later versions, including SQL Server 2012, that recommendation persists, but because of some optimizations (see my blog post) you usually do not need one-to-one – you may be fine with the number of tempdb data files equal to 1/4 to 1/2 the number of logical processor cores – which is what everyone apart from the official Microsoft guidance recommends.