:4TheLuvofSharePoint – Ivan Sanders

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

1 Comment