How to Move Tempdb ?

find the path of tempdb

sp_helpdb tempdb


name                                   filename

tempdev                           C:Program FilesMicrosoft SQL Server                                                                                                  MSSQL11.MSSQLSERVERMSSQLDATAtempdb.mdf

templog                             C:Program FilesMicrosoft SQLServer                                                                                            MSSQL11.MSSQLSERVERMSSQLDATAtemplog.ldf


To change the tempdb location use the following script:

USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev, FILENAME = ‘C:tempdbnewlocationTempdb.mdf’);
GO

ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog, FILENAME = ‘C:tempdbnewlocationTempdb.ldf’);
GO

sp_helpdb tempdb


USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev, FILENAME = ‘C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAtempdb.mdf’);
GO

ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog, FILENAME = ‘C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAtemplog.ldf’);
GO

sp_helpdb tempdb


cant change the recovery model

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