Updating the TEMPDB

Here's a quick/dirty little script to update your tempdb for SQL to 5GB for the tempdev (tempdb.mdf) file and 512MB for the templog (templog.ldf) file while at the same time turning off Auto Growth.

USE [master]

GO

ALTER DATABASE

[tempdb]

MODIFY FILE

(NAME = N'tempdev', SIZE = 5242880KB , FILEGROWTH = 0)

GO

ALTER DATABASE

[tempdb]

MODIFY FILE

(NAME = N'templog', SIZE = 524288KB , FILEGROWTH = 0)

GO

The restart your SQL Server Agent and SQL Server Services. Mine is on a cluster with the instance named SQL1.  If you are running on an MS Cluster, then you should probably do this via the Cluster Administrator console because even though the services may be online, the cluster admin console may report them to be in a failed state.

net stop SQLAgent$SQL1 & net stop MSSQL$SQL1

net start MSSQL$SQL1 & net start SQLAgent$SQL1

And yes, you can run a single command with that little ampersand (&) thingy there. :)

Here's an example of setting a TempDB with the following:

mdf file at 15GB, max size of 17.5GB, and growth at 10%

ldf file at 1.5GB, max size of 1.8 GB, and growth set to 10%

USE [master]
GO
ALTER DATABASE [tempdb]
MODIFY FILE ( NAME = N'tempdev',
SIZE = 15728640KB ,
MAXSIZE = 18350080KB,
FILEGROWTH = 10% )
GO
ALTER DATABASE [tempdb]
MODIFY FILE ( NAME = N'templog',
SIZE = 1572864KB ,
MAXSIZE = 1835008KB,
FILEGROWTH = 10% )
GO

Special note: If you copy/paste the code from the block-quotes above, and then try to run them in SQL Management Studio, you might notice them failing.  Try updating the single quotes from  around the file names, tempdev and templog.

Change the location of the TEMPDB

Use the ALTER DATABASE statement, specifying the logical file name as follows:

use master
go
Alter database tempdb modify file (name = tempdev, filename = 'E:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'E:\Sqldata\templog.ldf')
go

You should receive the following messages that confirm the change:
Message 1
File 'tempdev' modified in sysaltfiles. Delete old file after restarting SQL Server.
Message 2
File 'templog' modified in sysaltfiles. Delete old file after restarting SQL Server.
Using sp_helpfile in tempdb will not confirm these changes until you restart SQL Server.
Stop and then restart SQL Server