Skip to content

SQL Server Tempdb

    Find the path to SQL Server tempdb

    name		 filename
tempdev		C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\tempdb.mdf
templog		C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\templog.ldf

    To change the tempdb location use the following script:

    USE master;
GO

ALTER DATABASE tempdb
MODIFY FILE
(NAME = tempdev, FILENAME = 'C:\tempdbnewlocation\Tempdb.mdf');
GO

ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog, FILENAME = 'C:\tempdbnewlocation\Tempdb.ldf');
GO

sp_helpdb tempdb

USE master;
GO

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

ALTER DATABASE tempdb
MODIFY FILE
(NAME = templog, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\templog.ldf');
GO

sp_helpdb tempdb

    Change the recovery model

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

    Sizing the data and log files and auto growth 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

    Also See: