查看temp db
发表于:2024-10-19 作者:千家信息网编辑
千家信息网最后更新 2024年10月19日,use tempdb-- 每隔1秒钟运行一次,直到用户手工终止脚本运行while 1=1beginselect getdate()-- 从文件级看tempdb使用情况dbcc showfilestat
千家信息网最后更新 2024年10月19日查看temp dbuse 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秒钟运行一次,直到用户手工终止脚本运行
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.sysfiles
- where 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
空间
运行
空间申请
信息
正在
总体
情况
手工
文件
用户
脚本
视图
语句
分配
管理
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
网络安全专家兼职
网络安全第三十条
桌面数据库安装教程
触摸屏 软件开发
数据库怎么联合多表查询
中国万方数据库官网
nettime提示服务器未开启
我的世界服务器上锁
济南瀚盟网络技术有限公司怎么样
查看数据库中数据关键字
哪里有培训游戏软件开发的
爱采购网络安全监测装置
华为网络安全证书国家认可吗
智能软件开发专业职业定位
对话网络安全教学
数据库二级降序
苹果和华为网络技术
t40服务器如何u盘启动
华为网络安全指南
黄石软件开发公司排行
上网的网络安全知识
网络安全工作通讯报道
高青销售软件开发服务
运营商服务器登录许可
肥西县软件开发培训班
pod无法连接数据库
达梦数据库是非关系型数据库吗
讲述网络安全的电视剧
互联网大会云蚁科技
苹果手机神奇宝贝服务器