Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.