Microsoft SQL Server 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