Transfer SQL TempDB
Task:
How to transfer the TempDB.Problem:
MS SQL ran slow on 6Gb/s SAS hard drive (HHD). Moved the user database (DB) to PCIe x4 (i.e., 31.52 Gb/s), MLC solid-state (SSD) storage -and it still runs slow!Solution:
Does the TempDB run on the old HHD drive or the new MLC SSD? Consider how the TempDB can generate serious disk I/O. Transfer the TempDB to the high-speed SSD as well. N.B., this entire process gave new life to my old DPM2010 server.
Step 1: TSQL script to identify TempDB location:
Use master
GO
SELECT
name AS [LogicalName]
,physical_name AS [Location]
,state_desc AS [Status]
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO
TSQL output:
Step 2: TSQL script to move TempDB to new location.
tempdev C:\Program Files\Microsoft DPM\SQL\MSSQL10.MSDPM2010\MSSQL\DATA\tempdb.mdf
templog C:\Program Files\Microsoft DPM\SQL\MSSQL10.MSDPM2010\MSSQL\DATA\templog.ldf
Step 2: TSQL script to move TempDB to new location.
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'X:\SQL\Data\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'X:\SQL\Data\templog.ldf');
GO
TSQL output:
The file "tempdev" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "templog" has been modified in the system catalog. The new path will be used the next time the database is started.
Step 3: Restart SQL services
Step 4: Verify Change - See Step 1.
Conclusion:
SQL server runs quick with new storage. Disk queue lengths, for all disks, are a thing of the past. Individual results may vary based on load (duh).That's It!
References:
http://dbadiaries.com/tempdb-best-practiceshttp://dbadiaries.com/sql-server-tempdb-whats-it-for
http://www.mytechmantra.com/LearnSQLServer/How-to-Move-TempDB-to-New-Drive-in-SQL-Server/
