TempDB – Adding files and/or moving to another disk

USE master;
GO


/*
How many tempdb data files should be created?
http://www.sqlskills.com/blogs/paul/correctly-adding-data-files-tempdb/
*/
SELECT
    CASE 
        WHEN si.cpu_count <=8 THEN N'Use ' + CONVERT (varchar(3), si.cpu_count) + N' tempdb data files, equally sized' WHEN si.cpu_count > 8
            THEN N'Start with 8 tempdb data files, and add sets of four at a time, equally sized, until the contention is alleviated'
    END AS N'Recommended number of tempdb data files'
FROM
    sys.dm_os_sys_info AS si;
GO

/*
sys.master_files @ https://msdn.microsoft.com/en-us/library/ms186782.aspx
also see: sp_helpfile
*/
SELECT
      mf.name AS N'Logical Name'
    , mf.physical_name AS N'Physical Name'
    , mf.type_desc AS N'Type'
    , CONVERT (varchar (15), CONVERT (bigint, mf.size) * 8/1024) + N' MB' AS N'Size'
    , CASE mf.max_size
        WHEN - 1 
            THEN N'Unlimited'
        ELSE CONVERT (varchar (15), CONVERT (bigint, mf.max_size) * 8 /1024) + N' MB'
      END AS N'Max Size'
    , CASE mf.is_percent_growth
        WHEN 1
            THEN CONVERT (varchar( 15), mf.growth) + N' %'
        ELSE CONVERT (varchar(15), CONVERT (bigint, mf.growth) * 8/1024) + N' MB'
      END AS N'Growth'
FROM
    sys.master_files AS mf
WHERE
    mf.database_id = DB_ID (N'tempdb');
GO



/*
ALTER DATABASE @ https://msdn.microsoft.com/en-us/library/bb522469.aspx
*/
ALTER DATABASE tempdb
    MODIFY FILE (  NAME = tempdev
                 , FILENAME = N'C:\PdrSntsFrrr\Databases\TempDB\tempdb.mdf'
                 , SIZE = 10MB
                 , MAXSIZE = UNLIMITED
                 , FILEGROWTH = 2MB
                );
GO

ALTER DATABASE tempdb
    MODIFY FILE (  NAME = templog
                 , FILENAME = N'C:\PdrSntsFrrr\Databases\TempDB\templog.ldf'
                 , SIZE = 5MB
                 , MAXSIZE = UNLIMITED
                 , FILEGROWTH = 1MB
                );
GO

ALTER DATABASE tempdb
    ADD FILE (  NAME = tempdev2
              , FILENAME = N'C:\PdrSntsFrrr\Databases\TempDB\tempdb2.ndf'
              , SIZE = 10MB
              , MAXSIZE = UNLIMITED
              , FILEGROWTH = 2MB
             ),
             (  NAME = tempdev3
              , FILENAME = N'C:\PdrSntsFrrr\Databases\TempDB\tempdb3.ndf'
              , SIZE = 10MB
              , MAXSIZE = UNLIMITED
              , FILEGROWTH = 2MB
             ),
             (  NAME = tempdev4
              , FILENAME = N'C:\PdrSntsFrrr\Databases\TempDB\tempdb4.ndf'
              , SIZE = 10MB
              , MAXSIZE = UNLIMITED
              , FILEGROWTH = 2MB
             );
GO

/*
RESTART SQL Server service
*/


/*
Verify the file change.
*/
USE tempdb;
GO
EXEC sp_helpfile;
GO


/*
Delete the tempdb.mdf and templog.ldf files from their original location
*/