平武建站

查看tempdb

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



新闻名称：查看tempdb
网站链接：http://pwwzsj.com/article/jiiccp.html

其他资讯