Where Are tempdb_allocations Located?

The tempdb database in SQL Server is a system database that stores temporary objects. It's primarily used for several purposes such as temporary tables, table variables, and intermediate results of complex queries.

Understanding where tempdb allocations are located requires understanding SQL Server's storage structure:

1. tempdb Data Files:

- tempdb's physical storage is defined by data files (.mdf or .ndf) just like any other database. These files reside on disk. You can find the paths to these files by querying the sys.database_files system catalog view:

SELECT name, physical_name
FROM sys.database_files
WHERE database_id = DB_ID('tempdb');

- The locations specified in the physical_name column are where the actual data files of tempdb are stored.

2. Paging and Allocation Units:

- Inside these data files, data is stored in 8KB pages. SQL Server uses allocation units to manage how space is used and allocated.

- When tempdb needs to store temporary data (e.g., for a temporary table), it allocates space from available pages in its data files. This allocation is managed internally and is not directly visible as a folder or a specific file per allocation.

3. No Direct Folder Location for Allocations:

- Unlike some operating systems where temporary files might exist as physical files in a folder, tempdb's allocations are stored within the data files using its internal mechanisms. There is no directory or file that corresponds to specific individual allocations within tempdb.

4. SQL Server Internal Management:

- SQL Server handles the management of the tempdb data files, allocation, and deallocation of space internally. As such, you do not manage its internal allocations on a file-system level.

5. Implications of tempdb Location:

- The performance of tempdb operations (such as creating/dropping temporary objects) is heavily influenced by the storage where its data files reside. It’s crucial to ensure these files are on fast storage, such as SSDs, for optimal performance.

6. Important Note on tempdb Usage:

- tempdb is reset every time SQL Server restarts. Therefore, the allocations only last for the lifetime of the SQL Server instance.

In summary, while the physical data files for tempdb have a specified disk location, the actual allocations reside within these data files, managed internally by SQL Server. There are no visible file/folder structures for individual allocations as SQL Server manages this at the page and allocation unit level.

Learn more about this