View temp db
Use tempdb
-- run every 1 second until the user manually terminates the script
While 1 # 1
Begin
Select getdate ()
-- tempdb usage at the file level
Dbcc showfilestats
-- Query 1
-- returns the session information of all space applications
Select 'Tempdb' as DB, getdate () as Time
SUM (user_object_reserved_page_count) * 8 as user_objects_kb
SUM (internal_object_reserved_page_count) * 8 as internal_objects_kb
SUM (version_store_reserved_page_count) * 8 as version_store_kb
SUM (unallocated_extent_page_count) * 8 as freespace_kb
From sys.dm_db_file_space_usage
Where database_id = 2
-- Query 2
This management view reflects the overall allocation of tempdb space at that time
SELECT t1.session_id
T1.internal_objects_alloc_page_count, t1.user_objects_alloc_page_count
T1.internal_objects_dealloc_page_count, t1.user_objects_dealloc_page_count
T3.*
From sys.dm_db_session_space_usage t1
-- reflect each session cumulative space application
Sys.dm_exec_sessions as t3
-- Information for each session
Where
T1.session_id = t3.session_id
And (t1.internal_objects_alloc_page_count > 0)
Or t1.user_objects_alloc_page_count > 0
Or t1.internal_objects_dealloc_page_count > 0
Or t1.user_objects_dealloc_page_count > 0)
-- Query 3
Returns the running statement of the session that is running and has made a space request
SELECT t1.session_id
St.text
From sys.dm_db_session_space_usage as t1
Sys.dm_exec_requests as t4
CROSS APPLY sys.dm_exec_sql_text (t4.sql_handle) AS st
Where t1.session_id = t4.session_id
And t1.session_id > 50
And (t1.internal_objects_alloc_page_count > 0)
Or t1.user_objects_alloc_page_count > 0
Or t1.internal_objects_dealloc_page_count > 0
Or t1.user_objects_dealloc_page_count > 0)
Waitfor delay '0VOV 0RV 1'
End
Click (here) to collapse or open
Select replace (a.hostname as program_name',') as hostname,' + replace (hostname',') + as program_name
, loginame, j.*
From (
SELECT t1.session_id, t1.internal_objects_alloc_page_count*8.0 internal_objects_alloc_KB
T1.user_objects_alloc_page_count*8.0 user_objects_alloc_KB
T1.internal_objects_dealloc_page_count*8.0 internal_objects_dealloc_KB
T1.user_objects_dealloc_page_count*8.0 user_objects_dealloc_KB
St.text
From sys.dm_db_session_space_usage as t1
Sys.dm_exec_requests as t4
CROSS APPLY sys.dm_exec_sql_text (t4.sql_handle) AS st
Where t1.session_id = t4.session_id
And t1.session_id > 50
And (t1.internal_objects_alloc_page_count > 0)
Or t1.user_objects_alloc_page_count > 0
Or t1.internal_objects_dealloc_page_count > 0
Or t1.user_objects_dealloc_page_count > 0) as j
Left join
Sys.sysprocesses as a with (nolock) on a.spid=j.session_id
Where isnull (a. Loginame1)')'
Click (here) to collapse or open
Select j.sessionplayidreplace (s. Hostname as program_name',') as hostname,' + replace (s. Programprogramnames'',')
, s.loginame, db_name (s.dbid) AS DBname,j.text from
(SELECT t1.sessionencrypid.text
From sys.dm_db_session_space_usage as t1
Sys.dm_exec_requests as t4
CROSS APPLY sys.dm_exec_sql_text (t4.sql_handle) AS st
Where t1.session_id = t4.session_id
And t1.session_id > 50
And (t1.internal_objects_alloc_page_count > 0)
Or t1.user_objects_alloc_page_count > 0
Or t1.internal_objects_dealloc_page_count > 0
Or t1.user_objects_dealloc_page_count > 0) as j
Left join
Sys.sysprocesses as s with (nolock) on s.spid=j.session_id
Where isnull (s. Loginame1)')'
Click (here) to collapse or open
Select sum (convert (float,size) * (8192 1.0/sum 1024) / 1024 USED_PER from tempdb.dbo.sysfiles 1024) TMPFILE_GB, sum (convert (float,maxsize) * (8192 Universe 1024) / 1024 Universe 1024) MAX_TMPFILE_GB, (sum (size) * 1.0/sum (maxsize)) * 100 USED_PER from tempdb.dbo.sysfiles
Where name like 'tempdev%'
Select * from tempdb.dbo.sysfiles
Click (here) to collapse or open
Use tempdb
-- run every 1 second until the user manually terminates the script
While 1 # 1
Begin
Select getdate ()
-- tempdb usage at the file level
Dbcc showfilestats
-- Query 1
-- returns the session information of all space applications
Select 'Tempdb' as DB, getdate () as Time
SUM (user_object_reserved_page_count) * 8 as user_objects_kb
SUM (internal_object_reserved_page_count) * 8 as internal_objects_kb
SUM (version_store_reserved_page_count) * 8 as version_store_kb
SUM (unallocated_extent_page_count) * 8 as freespace_kb
From sys.dm_db_file_space_usage
Where database_id = 2
-- Query 2
This management view reflects the overall allocation of tempdb space at that time
SELECT t1.session_id
T1.internal_objects_alloc_page_count, t1.user_objects_alloc_page_count
T1.internal_objects_dealloc_page_count, t1.user_objects_dealloc_page_count
T3.*
From sys.dm_db_session_space_usage t1
-- reflect each session cumulative space application
Sys.dm_exec_sessions as t3
-- Information for each session
Where
T1.session_id = t3.session_id
And (t1.internal_objects_alloc_page_count > 0)
Or t1.user_objects_alloc_page_count > 0
Or t1.internal_objects_dealloc_page_count > 0
Or t1.user_objects_dealloc_page_count > 0)
-- Query 3
Returns the running statement of the session that is running and has made a space request
SELECT t1.session_id
St.text
From sys.dm_db_session_space_usage as t1
Sys.dm_exec_requests as t4
CROSS APPLY sys.dm_exec_sql_text (t4.sql_handle) AS st
Where t1.session_id = t4.session_id
And t1.session_id > 50
And (t1.internal_objects_alloc_page_count > 0)
Or t1.user_objects_alloc_page_count > 0
Or t1.internal_objects_dealloc_page_count > 0
Or t1.user_objects_dealloc_page_count > 0)
Waitfor delay '0VOV 0RV 1'
End
