- create a SP to record the temp file usage, and record the active session with SQL with requested paged from temp db
点击(此处)折叠或打开
- USE [DBCenter]
- GO
-
- CREATE PROCEDURE [dba].[sys_tmp_monitor_85]
- AS
- begin
- truncate table dbcenter..tmpdbmonitor_SQL
- insert into dbcenter..tmpdbmonitor_85
- 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,GETDATE() [DATE_TIME]
- from tempdb.dbo.sysfiles where name like 'tempdev%'
-
- insert into dbcenter..tmpdbmonitor_SQL
- select replace(a.hostname,' ','') as hostname ,''''+replace(program_name,' ','')+'''' as program_name
- , loginame,db_name(a.dbid) AS DBname, j.*,GETDATE() date_time
- 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 tempdb.sys.dm_db_session_space_usage as t1,
- tempdb.sys.dm_exec_requests as t4
- CROSS APPLY tempdb.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
- tempdb.sys.sysprocesses as a with(nolock) on a.spid=j.session_id
- where isnull(a.loginame,'') <>''
-
- insert into dbcenter..tmpdbmonitor_SQL_his
- select replace(a.hostname,' ','') as hostname ,''''+replace(program_name,' ','')+'''' as program_name
- , loginame,db_name(a.dbid) AS DBname, j.*,GETDATE() date_time
- 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 tempdb.sys.dm_db_session_space_usage as t1,
- tempdb.sys.dm_exec_requests as t4
- CROSS APPLY tempdb.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
- tempdb.sys.sysprocesses as a with(nolock) on a.spid=j.session_id
- where isnull(a.loginame,'') <>''
-
- end
if usage percent >=85 sent out mail
点击(此处)折叠或打开
- declare
- @used_per int,
- @sql varchar(8000),
- @sbj varchar(1000)
-
- begin
- select @used_per=(sum(size)*1.0/sum(maxsize))*100 from tempdb.dbo.sysfiles where name like 'tempdev%'
- print @used_per
- if @used_per>=85
- begin
- exec [DBCenter].[dba].[sys_tmp_monitor_85]
- set @sql='SELECT [hostname]
- ,[program_name]
- ,[loginame]
- ,[DBname]
- ,[session_id]
- ,[text]
- ,[date_time]
- FROM [DBCenter].[dbo].[tmpdbmonitor_SQL]'
- select @sbj=@@SERVERNAME
- set @sbj=@sbj+'tmpdb usage over 85 %'
- --print @sql
- EXEC msdb.dbo.sp_send_dbmail
- @profile_name = 'monitor',
- @recipients = '1234567@qq.com',
- @query = @sql ,
- @subject = @sbj,
- @attach_query_result_as_file = 1 ;
- end
- end
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16131092/viewspace-2138347/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/16131092/viewspace-2138347/