Search

Saturday, July 30, 2011

Move TempDB from one drive to another drive

Run this script to get the names of the files used for TempDB.
USE TempDB
Go
EXEC sp_helpfile
GO
Move the files to another drive
USE master
Go
ALTER DATABASE TempDB MODIFY FILE
(NAME = tempdev, FILENAME = 'd:datatempdb.mdf')
Go
ALTER DATABASE TempDB MODIFY FILE
(NAME = templog, FILENAME = 'e:datatemplog.ldf')
Go
Change the default size
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev', SIZE = 1024000KB )
USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', SIZE = 512000KB )
GO

Add file to TempDB filegroup (with Autogrow OFF)

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'D:\Data\tempdev2.ndf' , SIZE = 10240KB , FILEGROWTH = 0)
GO

No comments:

Post a Comment