Implementing Physical Database Structure - Microsoft SQL Server 2012 Performance Tuning - page 351

Always use SQL Server Configuration Manager to restart the SQL Server
Service. Do not restart SQL Server service directly from the Windows
Services MMC.

8. Once the SQL Server service is restarted, the tempdb database can be located at a

new specified location. To verify that the tempdb database is now at its new location,
run the following query:
--Switching database context to tempdb
USE tempdb
GO

--Examining new physical location
--of tempdb database
SELECT
name AS LogicalFileName
,physical_name AS PhysicalFilePath
FROM sys.database_files
GO

9. After executing the previous query, you should see a result similar to the one shown

in following screenshot:

How it works...

We first retrieved the current location of the tempdb database by querying the sys.
database_files system catalog view. We then altered the locations of the data file and
log file of the tempdb database using the ALTER DATABASE command, so that the data file
and log file are stored on different physical drives, M:\TempDB_Data\tempdb.mdf and
N:\TempDB_Log\templog.ldf , respectively, after SQL Server restarts. We then restarted
SQL Server service through SQL Server Configuration Manager. Finally, we queried the
sys.database_files system catalog view again, to verify the changes in location
of the tempdb database.