Using SQL DMVs – sys.dm_db_file_space_usage

You can use the sys.dm_db_file_space_usage dynamic management view to monitor the spaced used by tempdb for storing user/internal objects and version store.

sys.dm_db_file_space_usage has following columns:

1. database_id – will always be 2, database id of tempdb.

2. unallocated_extent_page_count – total number of free extents in the database file

3. version_store_reserved_page_count – pages allocated for version store

4. user_object_reserved_page_count – pages allocated for user objects, these are the permanent object you create explicitly in tempdb, system tables, temporary tables, table variables…

5. internal_object_reserved_page_count – pages allocated for internal objects

for details about other columns returned refer BOL.

 

Let’s see it in action:

– © 2011 – Vishal (http://SqlAndMe.com)

 

SELECT      *

FROM        sys.dm_db_file_space_usage

 

SELECT      *

INTO        #TempTable2

FROM        AdventureWorks2008R2.HumanResources.Employee

 

SELECT      *

FROM        sys.dm_db_file_space_usage

Result Set:

database_id file_id unallocated_extent_page_count version_store_reserved_page_countuser_object_reserved_page_count internal_object_reserved_page_count mixed_extent_page_count

———– ——- —————————– ——————————— ——————————- ———————————– ———————–

2           1       134832                        0                                64                              0                                   136

 

(1 row(s) affected)

 

(290 row(s) affected)

 

database_id file_id unallocated_extent_page_count version_store_reserved_page_countuser_object_reserved_page_count internal_object_reserved_page_count mixed_extent_page_count

———– ——- —————————– ——————————— ——————————- ———————————– ———————–

2           1       134824                        0                                72                              0                                   136

 

(1 row(s) affected)

The user_object_reserved_page_count changed to 72 from 64 after creating a temporary table.

And you can also see that the free extents available decreased from 134832 to 134824.

 

If you need to check currently available free space in tempdb, you can use below query:

SELECT      (unallocated_extent_page_count * 1.0) / 128

            AS [FreeSpcae (MB)]

FROM        sys.dm_db_file_space_usage

Result Set:

FreeSpcae (MB)

—————————————

1053.312500

 

(1 row(s) affected)

 

from http://sqlandme.com/2011/06/06/using-sql-dmvs-sys-dm_db_file_space_usage/