All you have to do is to modify the path in the script and run it in a new query !


USE master
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'e:\tempdb01.mdf')
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'f:\tempdb01.ldf')
GO

NB: You have to restart SQL services once your done !

Tagged with:  

How to add file and log to SQL tempdb

On 12 February 2014, in SQL, by Himselff

As Microsoft best practice , we need one tempdb file per CPU and normaly on different disk, here how to do it !

In Query Analyser :

  • Pour le fichier :

USE [master]
GO
ALTER DATABASE [tempdb]

ADD FILE (
 NAME = tempdb04,
 FILENAME = 'k:\MSSQL\Data\tempdb04.ndf',
 SIZE= 20GB,
 FILEGROWTH = 0
 );
GO

  • Pour le log :

USE [master]
GO
ALTER DATABASE [tempdb]

ADD LOG FILE (
 NAME = tempdblog04,
 FILENAME = 'l:\MSSQL\Data\tempdblog04.ldf',
 SIZE= 10GB,
 FILEGROWTH = 0
 );
GO

Tagged with: