Database Related Dynamic Management Views

from http://www.sqlserverspecialists.com/2013/03/database-related-dynamic-management.html

 

Database Related  Dynamic Management Views

 

NOTE:For SQL Server 2005, these DMVs are only usable for tempdb.

 

sys.dm_db_file_space_usage

 

Returns space usage information for each file in tempdb.

 

sys.dm_db_session_space_usage

 

Returns the number of pages allocated and deallocated by each session, user or internal, in tempdb.

 

sys.dm_db_partition_stats

 

Returns one row per partition in the database, showing page and row-count information for every partition in the current database, including the space used to store and manage in-row data, LOB data, and row-overflow data for all partitions in a database.

 

sys.dm_db_task_space_usage

 

Returns page allocation and deallocation activity by task for tempdb, excluding IAM pages.

 

 

To determine the amount of space used by internal and user objects in tempdb:

 

SELECT SUM(internal_object_reserved_page_count) AS internal_pages,

(SUM(internal_object_reserved_page_count)*1.0/128) AS internal_space_MB,

SUM(user_object_reserved_page_count) AS user_pages,

(SUM(user_object_reserved_page_count)*1.0/128) AS user_space_MB

FROM sys.dm_db_file_space_usage;

 

To see the total space consumed by internal objects in currently running tasks in tempdb:

 

SELECT session_id,

SUM(internal_objects_alloc_page_count) AS task_pages,

SUM(internal_objects_dealloc_page_count) AS task_dealloc_pages

FROM  sys.dm_db_file_space_usage;