Monthly Archives: January 2019

Change temdb file location

simply move the files to a new location by generating the script. For a pretty output you can use the execute to text function in SSMS.

USE tempdb
GO
-- don't forget to use a \ at the end of your new location
declare @NewLocation nvarchar(512) = '<YourNewFileLocation>\'

SET NOCOUNT ON;
declare @ChangeTempDBLocation table
(

     xName nvarchar(128)
	,xFileId int
	,XFileName nvarchar(512)
	,xFileGroup nvarchar(128)
	,xSize nvarchar(128)
	,xMaxSize nvarchar(128)
	,xGrowth nvarchar(128)
	,xUsage nvarchar(128)
)
insert into @ChangeTempDBLocation
exec sp_helpfile

SELECT 'USE [master]'+CHAR(13)+'GO'
UNION ALL
select 
concat('ALTER DATABASE [tempdb] MODIFY FILE (NAME = ',xName,', FILENAME = ''',@NewLocation,reverse(left(reverse(xFileName),charindex('\',reverse(XFilename))-1)),''')')
--reverse(left(reverse(xFileName),charindex('\',reverse(XFilename))-1))
from @ChangeTempDBLocation