------- Space problem of the TEMPDB database -----&#

Today, I accidentally saw the SYS. dm_db_session_space_usage DMV. I can use this DMV to confirm it.TempdbAnd how to determine the space used by the version storage area, internal objects, and user objects.

Select
Sum (unallocated_extent_page_count) as [Free pages],
(Sum (unallocated_extent_page_count) * 1.0/128) as [free space in MB]
From
SYS. dm_db_file_space_usage;

/*
Free pages free space in MB
-----------------------------------------------------------
808 6.312500
*/

This SQL statement can be foundTempdbTotal number of available pages and total available space for all files in.

How can I check the total number of pages and total space used by the version storage area?

Select
Sum (version_store_reserved_page_count) as [version store pages used],
(Sum (version_store_reserved_page_count) * 1.0/128) as [version store space in MB]
From
SYS. dm_db_file_space_usage;

/* Version store pages used version store space in MB
---------------------------------------------------------------
0 0.000000

(One Line is affected )*/

The total number of pages and total space used by internal objects can be queried as follows:

SELECT
SUM (internal_object_reserved_page_count) AS [internal object pages used],
(SUM (internal_object_reserved_page_count) * 1.0/128) AS [internal object space in MB]
FROM
Sys. dm_db_file_space_usage;

/*
Internal object pages used internal object space in MB
-----------------------------------------------------------------
16 0.125000

(One row is affected)
*/

Check the total number of pages and total space used by the user object as follows:

SELECT
SUM (user_object_reserved_page_count) AS [user object pages used],
(SUM (user_object_reserved_page_count) * 1.0/128) AS [user object space in MB]
From
SYS. dm_db_file_space_usage;

/*
User object pages used user object space in MB
-------------------------------------------------------------
40 0.312500

(One row is affected)
*/

TempdbCheck the total disk space used by all files in:

Select
Sum (size) * 1.0/128 as [size in MB]
From
Tempdb. SYS. database_files

/*
Size in MB
---------------------------------------
8.750000

(One row is affected)
*/

For more information about the space used for monitoring and query, see books online.

There are two main methods.

The first method is to check the batch data, which is less than the second method.

The second method can be used to identify specific queries, temporary tables, or table variables that occupy disk space. To obtain the answer, you must collect more data.

If batch processing involves a large number of queries. Job is required for round-robin.

The Code comes from books online:

A. Obtain the space occupied by internal objects in all tasks currently running in each session.

The following example creates a viewall_task_usage. After the query is executed, the view returnsTempdbTotal space used by internal objects in all tasks currently running in.

Create view all_task_usage
As
Select session_id,
Sum (internal_objects_alloc_page_count) as task_internal_objects_alloc_page_count,
Sum (internal_objects_dealloc_page_count) as task_internal_objects_dealloc_page_count
From SYS. dm_db_task_space_usage
Group by session_id;
Go

B. Obtain the space occupied by running tasks and internal objects of completed tasks in the current session.

The following example creates a viewall_session_usage. After the query is executed, the view returnsTempdbSpace used by running tasks and all internal objects in completed tasks.

Create view all_session_usage
As
Select r1.session _ id,
R1.internal _ objects_alloc_page_count
+ R2.task _ internal_objects_alloc_page_count as session_internal_objects_alloc_page_count,
R1.internal _ objects_dealloc_page_count
+ R2.task _ internal_objects_dealloc_page_count AS session_internal_objects_dealloc_page_count
FROM sys. dm_db_session_space_usage AS R1
Inner join all_task_usage AS R2 ON R1.session _ id = R2.session _ id;
GO

There is still a lot of content, so I will not write it. Please refer to the online books series.