查看temp db

use tempdb                                                               

— 每隔1秒钟运行一次，直到用户手工终止脚本运行

while 1=1                                                               

begin                                                                   

select getdate()                                                        

— 从文件级看tempdb使用情况

dbcc showfilestats                                                      

— Query 1

— 返回所有做过空间申请的session信息

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

— 这个管理视图能够反映当时tempdb空间的总体分配

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 ,                               

— 反映每个session累计空间申请

sys.dm_exec_sessions as t3

— 每个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

— 返回正在运行并且做过空间申请的session正在运行的语句

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 ‘0:0:1’                                                    

end          

点击(此处)折叠或打开

1. select replace(a.hostname,‘ ‘,”) as hostname ,””+replace(program_name,‘ ‘,”)+”” as program_name
   
2. , loginame, j.*
3. from (
4. SELECT t1.session_id, t1.internal_objects_alloc_page_count*8.0 internal_objects_alloc_KB,
5. t1.user_objects_alloc_page_count*8.0 user_objects_alloc_KB,
6. t1.internal_objects_dealloc_page_count*8.0 internal_objects_dealloc_KB,
7. t1.user_objects_dealloc_page_count*8.0 user_objects_dealloc_KB,
8. st.text
9. from sys.dm_db_session_space_usage as t1,
10. sys.dm_exec_requests as t4
11. CROSS APPLY sys.dm_exec_sql_text(t4.sql_handle) AS st
12. where t1.session_id = t4.session_id
13.   and t1.session_id >50
14.   and (t1.internal_objects_alloc_page_count>0
15.     or t1.user_objects_alloc_page_count >0
16.     or t1.internal_objects_dealloc_page_count>0
17.     or t1.user_objects_dealloc_page_count>0) ) as j
18. left join
19.     sys.sysprocesses as a with(nolock) on a.spid=j.session_id
20.     where isnull(a.loginame,”) <>”

点击(此处)折叠或打开

1. select j.session_id,replace(s.hostname,‘ ‘,”) as hostname ,””+replace(s.program_name,‘ ‘,”)+”” as program_name
   
2. , s.loginame, db_name(s.dbid) AS DBname,j.text from
3.                                              
4.  ( SELECT t1.session_id,st.text
5. from sys.dm_db_session_space_usage as t1,
6. sys.dm_exec_requests as t4
7. CROSS APPLY sys.dm_exec_sql_text(t4.sql_handle) AS st
8.                
9.  where t1.session_id = t4.session_id
10.     and t1.session_id >50
11.     and (t1.internal_objects_alloc_page_count>0
12.     or t1.user_objects_alloc_page_count >0
13.     or t1.internal_objects_dealloc_page_count>0
14.     or t1.user_objects_dealloc_page_count>0)) as j
15.                                              
16. left join
17.     sys.sysprocesses as s with(nolock) on s.spid=j.session_id
18.         where isnull(s.loginame,”) <>”

点击(此处)折叠或打开

1. select sum(convert(float,size) * (8192/1024)/1024/1024) TMPFILE_GB, sum(convert(float,maxsize) * (8192/1024)/1024/1024) MAX_TMPFILE_GB,(sum(size)*1.0/sum(maxsize))*100 USED_PER from tempdb.dbo.sysfiles
   
2. where name like ‘tempdev%’
3. select * from tempdb.dbo.sysfiles

点击(此处)折叠或打开

1. use tempdb
   
3. — 每隔1秒钟运行一次，直到用户手工终止脚本运行
5. while 1=1
7. begin
9. select getdate()
11. — 从文件级看tempdb使用情况
13. dbcc showfilestats
15. — Query 1
17. — 返回所有做过空间申请的session信息
19. Select ‘Tempdb’ as DB, getdate() as Time,
21.     SUM (user_object_reserved_page_count)*8 as user_objects_kb,
23.     SUM (internal_object_reserved_page_count)*8 as internal_objects_kb,
25.     SUM (version_store_reserved_page_count)*8 as version_store_kb,
27.     SUM (unallocated_extent_page_count)*8 as freespace_kb
29. From sys.dm_db_file_space_usage
31. Where database_id = 2
33. — Query 2
35. — 这个管理视图能够反映当时tempdb空间的总体分配
37. SELECT t1.session_id,
39. t1.internal_objects_alloc_page_count, t1.user_objects_alloc_page_count,
41. t1.internal_objects_dealloc_page_count , t1.user_objects_dealloc_page_count,
43. t3.*
45. from sys.dm_db_session_space_usage t1 ,
47. — 反映每个session累计空间申请
49. sys.dm_exec_sessions as t3
51. — 每个session的信息
53. where
55. t1.session_id = t3.session_id
57. and (t1.internal_objects_alloc_page_count>0
59. or t1.user_objects_alloc_page_count >0
61. or t1.internal_objects_dealloc_page_count>0
63. or t1.user_objects_dealloc_page_count>0)
65. — Query 3
67. — 返回正在运行并且做过空间申请的session正在运行的语句
69. SELECT t1.session_id,
71. st.text
73. from sys.dm_db_session_space_usage as t1,
75. sys.dm_exec_requests as t4
77. CROSS APPLY sys.dm_exec_sql_text(t4.sql_handle) AS st
79.  where t1.session_id = t4.session_id
81.    and t1.session_id >50
83. and (t1.internal_objects_alloc_page_count>0
85. or t1.user_objects_alloc_page_count >0
87. or t1.internal_objects_dealloc_page_count>0
89. or t1.user_objects_dealloc_page_count>0)
91. waitfor delay ‘0:0:1’
93. end