After having analyzed this TechNet article http://technet.microsoft.com/en-us/library/cc298801(office.12).aspx regarding Physical storage recommendations (Office SharePoint Server) I have decided to create a post related to tempdb.

Here you have a list of actions that you can apply on tempdb

1. Check where temp db is located

SELECT name, physical_name AS CurrentLocation

FROM sys.master_files

WHERE database_id = DB_ID(N’tempdb’);

GO

2. Change the location of temp_db

USE master;

GO

ALTER DATABASE tempdb

MODIFY FILE (NAME = tempdev, FILENAME = ‘F:SQLDatatempdb.mdf’);

GO

Results

In order for the change to have effect you need to restart the instance of SQL Server.

4. Verify the file change.

SELECT name, physical_name AS CurrentLocation, state_desc

FROM sys.master_files

WHERE database_id = DB_ID(N’tempdb’);

5. You have to delete the tempdb.mdf files from the original location

6. Move the tempdb by specifying the maximum size

ALTER DATABASE tempdb   

MODIFY FILE (NAME = tempdev,   

FILEGROWTH = 10% ,   

MAXSIZE = 7168MB,   

SIZE=7168MB,   

FILENAME = ‘C:databasestempd.mdf’)   

GO

7. Split the tempdb into equal file sizes, 1 per processor

ALTER DATABASE tempdb ADD FILE   

(NAME = tempdev_1,   

FILEGROWTH = 10% ,   

MAXSIZE = 7164MB,   

SIZE=7164MB ,   

FILENAME = ‘D:databasestempdb_2.mdf’)   

GO 

Additional information in those articles:

http://msdn.microsoft.com/en-us/library/ms175527.aspx Optimizing tempdb Performance

http://msdn.microsoft.com/en-us/library/ms190768.aspx tempdb Database

http://technet.microsoft.com/en-us/library/hh292622.aspx Best practices for SQL Server 2008 in a SharePoint Server 2010 farm