HOW TO MOVE TEMP DB IN SQL SERVER 2005/2008 R2
All temporary results from stored procedures of all databases are temporary stored in the tempdb. Because of its central part in the execution of T-SQL statements it can easily become a performance bottlekneck. To mitigate this it is best to move the tempdb to a fast drive instead of its default location in c:\. This is especially true for a SharePoint database server. All uploaded documents go through the tempdb before being put into the content database.
See also article: How to optimze temp db in SQL Server 2005/2008 R2 by splitting the database in multiple files
Steps:
1. Determine the logical file names for the tempdb database:
use tempdbgosp_helpfilego |
The logical name for each file is contained in the name column.
2. Move the temp db using the ALTER DATABASE statement, specifying the logical file name as follows:
use mastergo alter database tempdb modify file (name = tempdev, filename = 'E:\MyFolder\tempdb.mdf')go alter database tempdb modify file (name = templog, filename = 'E:\MyFolder\templog.ldf')go |
3. Stop and then restart SQL Server.