Microsoft SQL Server System database - TempDb TempDB database details
Example
Below query can be used to get TempDB database details:
USE [MASTER]
SELECT * FROM sys.databases WHERE database_id = 2
OR
USE [MASTER]
SELECT * FROM sys.master_files WHERE database_id = 2
With the help of below DMV, you can check how much TempDb space does your session is using. This query is quite helpful while debugging TempDb issues
SELECT * FROM sys.dm_db_session_space_usage WHERE session_id = @@SPID
