Moving TempDB can be easy but tricky

There are numerous considerations when it comes to the location of the TempDB files of a SQL Server instance.Think of TempDB as a common workload pool, potentially shared by all processes in the SQL Server instance. I.e. any process could carry out work which could implicitly or explicitly concern TempDB.

For example, we can have user-created code which explicitly uses objects created in the TempDB (temp tables). Also, we could have user-created code which involves sort and aggregation operations, which depending on the system configuration and hardware, might turn to use TempDB. In the second case, the processes are implicitly concerning TempDB. Also, there are system processes which always use TempDB, i.e. change tracking, row versioning.

The correct placement of the TempDB really depends on the system requirements and its workload. It is a real draw-back that there is only one TempDB entity for each instance of SQL Server and it is shared between all kinds of workloads. And also, the TempDB is located on a disk.

Disks are slower than memory. No matter what.

This is why it is important to place the TempDB on its own disk, at the least. And if it is worth the investment (i.e. if you need performance for heavy TempDB related workloads), then the TempDB disk has to be as fast as possible.

Here is a script I wrote, which will help you move the TempDB to a different disk. What this script does is, it checks if the new folder exists, it recommends that you check the SQL Server service account for having the right permissions to the folder and then it generates a script which will help you move the files.

Disclaimer: execute this script at your own risk. Read the notes in it and execute the script in a ‘Results to Text’ mode. The script DOES NOT make any changes to your system. You will have to copy and execute parts of the script if you would like to make changes.

 

?Download download.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
SET NOCOUNT ON
PRINT '****************************************************************************'
PRINT 'This script is developed by Feodor Georgiev, rights reserved, but not limited. Use at your own risk and after proper review and testing.
The following script helps you move the tempdb to a new location.
The script should be executed in a ''Results to text'' mode in the Management Studio, and it will print out step-by-step instructions on how to prepare and move the tempdb.
The script does not make changes to your system, it only prints out instructions and a script which will help you move the TempDB files in a easier way. '
PRINT '****************************************************************************'
PRINT ''
-- set the destination folder for the tempdb
DECLARE @tempDBNewFolder NVARCHAR(500)
SET @tempDBNewFolder = 'E:\Sys\Tempdb\'
 -- make sure you enter the trailing slash '\'
 
-- check if the folder exists
DECLARE @file_path NVARCHAR(500)
DECLARE @file_exists INT
 
SET @file_path = @tempDBNewFolder + 'nul'
 
EXEC master.dbo.xp_fileexist @file_path, @file_exists OUTPUT
 
PRINT 'Step 1: Folder ' + ISNULL(@tempDBNewFolder, 'NULL') + ' '
    + CASE WHEN @file_exists = 1 THEN 'exists'
           ELSE 'DOES NOT EXIST!!! Do not make changes to the database! '
      END
 
-- find the sql server account
DECLARE @ServiceaccountName VARCHAR(250)
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
    N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER', N'ObjectName',
    @ServiceAccountName OUTPUT, N'no_output'
 
PRINT ''
PRINT 'Step 2: make sure that the ' + @ServiceaccountName
    + ' account has proper permissions to read and write to the '
    + @tempDBNewFolder + ' folder.'
 
-- generate the alter database script
 
PRINT ''
PRINT 'Step 3: Copy the following script which will alter the TempDB location. '
PRINT 'NOTE: Make sure that the step 1 and 2 bove are complete correctly, otherwise your SQL Server may not start after restarting the serrvice.'
 
PRINT ''
SELECT  'USE master
GO'
UNION ALL
SELECT  'ALTER DATABASE tempdb MODIFY FILE (NAME = ' + mf.name
        + ', FILENAME = ''' + @tempDBNewFolder
        + REVERSE(LEFT(REVERSE(physical_name),
                       CHARINDEX('\', REVERSE(physical_name)) - 1)) + ''')
GO'
FROM    sys.master_files mf
WHERE   [database_id] = 2
 
PRINT ''
PRINT 'Step 4: After copying the above script, paste it in a new window of the Management Studio and review the script before executing. '
PRINT ''
PRINT '!!! EXECUTE AT YOUR OWN RISK !!! '
 
PRINT ''
PRINT 'Step 5: WHAT IF module: After making changes to the TempDB, you will need to restart the SQL Server service.
If the service does not start, you have two options:
 
1) buy a one-way ticket to a warm country and stay there forever
2) open the CMD as an administrator, run ''NET START ServiceName /f /T3608'' (no quotes and replace the ServiceName) and then use ''SQLCMD –S Machine\Instance'' to connect to the machine and run a script to alter the tempdb files to a proper location. '

 

Comments are closed.