Amount of space (total, used and free) in tempdb

In this post we will see how to determine the amount of total and free space tempdb has.

Tempdb is one of the system databases and there is only one by SQL Instance. Tempdb is used by all of the other databases when queries require to work with temporary data. 

It's important to mention that if tempdb has issues it will have an impact in all other databases on the Instance, so you see now why it is important keeping it problem free.

The following script uses sys.dm_db_file_space_usage to check the total size of tempdb by summing the values of the following columns: 

• user_object_reserved_page_count
• internal_object_reserved_page_count
• version_store_reserved_page_count
• mixed_extent_page_count
• unallocated_extent_page_count

Used space is calculated the same way just skipping "unallocated_extent_page_count" and finally the Free Space is calculated using this "unallocated_extent_page_count" column.

As all column values are in database pages, we multiply this value by 8 (one page is 8KB) and divided by 1024 to calculate the value Megabytes.


--Amount of space (total, used, and free) in tempdb
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT SUM(user_object_reserved_page_count
+ internal_object_reserved_page_count
+ version_store_reserved_page_count
+ mixed_extent_page_count
+ unallocated_extent_page_count) * (8.0/1024.0)
AS [TotalSizeOfTempDB(MB)]
, SUM(user_object_reserved_page_count
+ internal_object_reserved_page_count
+ version_store_reserved_page_count
+ mixed_extent_page_count) * (8.0/1024.0)
AS [UsedSpace (MB)]
, SUM(unallocated_extent_page_count * (8.0/1024.0))
AS [FreeSpace (MB)]
FROM sys.dm_db_file_space_usage

The output will look as follows.







Monitoring this output will help you determine patterns of tempdb usage during a period of time, this way you can whether schedule your jobs to run when there is less tempdb usage or increase the allocated space according to the demand.