查看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
, 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.loginame,'') <>''
, s.loginame, db_name(s.dbid) AS DBname,j.text from
( 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)) as j
left join
sys.sysprocesses as s with(nolock) on s.spid=j.session_id
where isnull(s.loginame,'') <>''
where name like 'tempdev%'
select * from tempdb.dbo.sysfiles
-- 每隔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秒钟运行一次，直到用户手工终止脚本运行
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
点击(此处)折叠或打开
select replace(a.hostname,' ','') as hostname ,''''+replace(program_name,' ','')+'''' 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.loginame,'') <>''
点击(此处)折叠或打开
select j.session_id,replace(s.hostname,' ','') as hostname ,''''+replace(s.program_name,' ','')+'''' as program_name, s.loginame, db_name(s.dbid) AS DBname,j.text from
( 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)) as j
left join
sys.sysprocesses as s with(nolock) on s.spid=j.session_id
where isnull(s.loginame,'') <>''
点击(此处)折叠或打开
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.sysfileswhere name like 'tempdev%'
select * from tempdb.dbo.sysfiles
点击(此处)折叠或打开
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
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系本站删除。
