当前位置：首页 > 数据库 > MSSQL教程 > monitor temp db usage for MSSQL > 正文

monitor temp db usage for MSSQL

来源：这里教程网 时间：2026-03-02 11:12:07 作者：
    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
2. create a job which will get the tmpdb usage info
   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


编辑推荐：

相关推荐

最新软件资讯

DOS批处理命令实现自动复制指定的文件夹到其他位置
DOS批处理命令实现自动复制指定的文件夹到其他位置

热文推荐

天极热推