HEELPBOOK - SQL Server 2008/2008 R2 - Move tempdb to Improve Performance ################################

By default, tempdb is placed on the same drive that SQL Server is installed on. This can impair performance as tempdb is frequently used to store temporary tables and objects. If the hard drive is being accessed for other functions, it can result in sluggish performance by the database as well as any software that is using the database.

For optimum performance, tempdb should be on a SATA drive instead of an IDE drive and should not be on the same drive as the SQL Server software or the operating system (boot drive).

1. Open SQL Server Management Studio.

2. Connect to the desired server.

3. Click the New Query button.

4. Copy and paste the following into the query pane:


USE master; GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = '[new location]\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = '[new location]\templog.ldf');
GO


5. Change [new location] in the pasted code (for both the tempdb.mdf and templog.ldf files) to the path of the new location.

6. Click Execute.

7. Go to the Control Panel and then Administrative Tools. Select Services.

8. Stop and restart SQL Server (MSSQLSERVER).

9. Go back to SQL Server Management Studio and open a new query pane.

10. Execute the following to verify that tempdb was moved to the desired location:


SELECT name, physical_name FROM sys.master_files
WHERE database_id = DB_ID('tempdb');


11. You should see the path to the new location in the physical_name column.

12. If the move was successful, go to the old location and delete the tempdb.mdf andtempdb.ldf files as they are no longer needed.




############ ARTICLE INFO #############

Article Month: June
Article Date: 20/06/2012
Permalink: http://heelpbook.altervista.org/2012/sql-server-20082008-r2-move-tempdb-to-improve-performance/
Source: http://www.tech-recipes.com/rx/19178/sql-server-20082008-r2-move-tempdb-to-improve-performance/
Language: English

View more articles on: http://www.heelpbook.net/
Follow us on Facebook: http://it-it.facebook.com/pages/HeelpBook/100790870008832
Follow us on Twitter: https://twitter.com/#!/HeelpBook
Follow us on RSS Feed: http://feeds.feedburner.com/Heelpbook
Follow us on Delicious: http://delicious.com/heelpbook