MSSQL - Change tempdb mdf and ldf file location

sp_helpdb tempdb




USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\Tempdb.mdf');
GO

ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SMRPLM\MSSQL\DATA\tempdb_mssql_8.ndf');
GO
USE master;
GO

###########################ALSO  ##########################################

We cant change recovery mode from simple to full in temp db





USE [master]
GO
ALTER DATABASE [tempdb] SET RECOVERY FULL
GO

--Sizing the data and log files and autogrowth property

USE [master]

GO

ALTER DATABASE [tempdb]

MODIFY FILE ( NAME = N'tempdev', SIZE = 102400KB , FILEGROWTH = 20480KB )--<< pre sizing data, auto growth

GO

ALTER DATABASE [tempdb]

MODIFY FILE ( NAME = N'templog', SIZE = 20480KB , FILEGROWTH = 20480KB )--<< pre sizing log, auto growth

GO