Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

I run 3 SQL 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?

share|improve this question
    
Please add the query you are using to your question. –  Mark Storey-Smith Apr 18 '13 at 2:32
    
Are you sure? The old files need to be cleaned up manually. Check the new location to see if new files were created. –  Jon Seigel May 18 '13 at 15:47

2 Answers 2

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.

share|improve this answer
    
Thanks StrayCat. Yes, that sounds right. –  Sandro Apr 18 '13 at 13:04
    
Thanks StrayCat. Yes, that sounds right. However, new TempdB files are not being created in the new location specified, when I restart that specific instance. So I am asking if I have to restart the whole SQL Server (3 instances), when what I read indicates just restarting the instance that was modified. Thanks. –  Sandro Apr 18 '13 at 13:18
    
You don't need to reboot the machine, just restart the sql instance. Did you verify the change is showing up in tempdb.sys.database_files? –  StrayCatDBA Apr 18 '13 at 17:40

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.