2 ways SQL Server views Database space allocation

Method 1, Sys.dm_db_file_space_usage.

Method 2, Sys.dm_db_session_space_usage.

--------------------------------------------------------------------------------------------------------------- -----------------------------------------------

Method 1,

Sys_dm_db_file_space_usage it is measured in pages (page) and returns one row for each database file (without the log file)

It is time to use it to the specified database before it can be used. It does not return information for all databases in the current instance, for the current database.

Total_page_count: Total number of pages

Allocated_extent_page_count: The total number of pages in the allocated area of the file.

Unallocated_extent_page_count: The total number of pages in the unallocated area of the file.

User_object_reserved_page_count: The total number of pages allocated from the consolidated zone for user objects in the database. The count includes pages that are not used in the allocated area.

Internal_object_reserved_page_count: The total number of pages allocated from the consolidated zone to the internal objects in the file.

Mixed_extent_page_count: The total number of allocated and unassigned pages in the allocated mixed area of the file.

Example

Select
Db_name (usage.database_id) as DBName,
SUM (usage.unallocated_extent_page_count) as Unallocatedpage,
SUM (usage.allocated_extent_page_count) as Allocatedpage,
SUM (usage.internal_object_reserved_page_count) as Internalpage,
SUM (usage.user_object_reserved_page_count) as Userobjectpage,
SUM (usage.mixed_extent_page_count) as mixedpage from Sys.dm_db_file_space_usage as usage
GROUP BY usage.database_id
Go

Method 2,

Sys.dm_db_session_space_usage returns the number of pages allocated and freed per session for the database, and it is only for tempdb space usage, other databases cannot see

User_objects_alloc_page_count: The number of pages reserved or allocated by the session for the user object.

User_objects_dealloc_page_count: The number of pages freed by the session and no longer reserved for user objects.

Internal_objects_alloc_page_count: The number of pages reserved or allocated for internal objects by the session.

Internal_objects_dealloc_page_count: The number of pages freed by the session and no longer reserved for internal objects.

Example

Select
Db_name (usage.database_id) as DBName,
USAGE.SESSION_ID,
Usage.user_objects_alloc_page_count,
Usage.user_objects_dealloc_page_count,
Usage.internal_objects_alloc_page_count,
Usage.internal_objects_dealloc_page_count
from sys.dm_db_session_space_usage usage;
Go

Summary:

Sys.dm_db_session_usage only for tempdb.

2 ways SQL Server views Database space allocation