SQL Server's tempdb system database handles temporary tables, sorting operations, indexing, cursors, and internal query processing. It often faces disk space usage and allocation challenges due to suboptimal default configurations during SQL Server installation.

MEDITECH Best Practices

MEDITECH recommends allocating 25-30% of the total server dataspace to tempdb. For instance, a 2TB data space warrants approximately 500GB for tempdb. Key recommendations include:

• Dedicated drive allocation for tempdb
• Space distributed evenly across nine files (eight data, one log)
• Files sized to maximum capacity with autogrowth disabled

Step 1: Add New Data Files

Create a directory on a new drive (such as a T: drive with MEDIFILES folder). Within SQL Management Studio, navigate to Databases, then System Databases, then tempdb, right-click, select Properties, then click Files in the left pane. Click Add to create additional files until reaching eight data files. For each new file:

• Set size to (drive size divided by 9) in megabytes
• Configure path and name (e.g., T:\MEDIFILES\temp3.ndf)
• Disable Autogrowth via the ellipsis button

Step 2: Create a Script to Move Existing Files

Use a SQL script that generates ALTER DATABASE statements for relocating existing tempdb files. The script produces output showing commands like:

ALTER DATABASE tempdb MODIFY FILE (NAME = [tempdev], FILENAME = 'T:\MEDIFILES\tempdev.mdf');

Review these statements carefully before execution.

Step 3: Restart SQL Instance

Shut down DR transfer background jobs in MIS before restarting SQL Server (a Windows OS reboot is unnecessary). After restart, verify tempdb file properties in Management Studio and resize original files to match newly-added ones.

For additional tempdb configuration guidance, see Brent Ozar's tempdb cheat sheet and Microsoft's official tempdb documentation.