SQL Query: Move TempDb Files to separate LUNs
Tempdb Multiple Files
One of the important issues when hosting multiple Content Dbs with multiple terabytes data is to ensure that you have created a TempDB with additional files of the same size and once created move teach of the files to their own Volume.
Do NOT use ISCSI for SQL Dbs
In this case we are using a NetApp SAN with Fiber Chanel HBAs as iSCSI does NOT meet our SQL performance requirements. I Microsoft provides multiple whitepapers against using iSCSI for SQL Dbs. Also, I have seen performance degradation using iSCSI once the ContentDBs reach about 50GB in total size. Using Fiber Chanel I have been able to host multiple terabytes of ContentDbs without any degradation in performance.
Hardware
This server has 2 PROCS with 24 Cores, and 128GB of RAM with tempdb having 1 MDF, 2 NDF, and 1 LDF files on separate LUNS.. We used this script to move the original tempdb files to the SAN and then once provisioned to move the NDF files to separate LUNs.
NAME = Tempdb file Logical Name
FILENAME = the New path with existing filename where you want the file moved
Screenshot of OLD Locations
Screenshot NEW Locations
-- Query to Move tempDB SELECT name, physical_name AS CurrentLocation FROM sys.master_files WHERE database_id = DB_ID(N'tempdb'); GO USE master; GO ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'K:\MSSQL\Data\tempdb.mdf'); GO ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'L:\MSSQL\Data\templog.ldf'); GO SELECT name, physical_name AS CurrentLocation, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'tempdb');
Note: you will need to restart the SQL Server Instance to have the move take affect
Download SET_TempDBLocation.ps1
Ivan
Thank you for sharing such as wonderful articles. Those are very useful to MSBI learners. MSBI Online Training