monitor temp db usage for MSSQL的示例代码

这篇文章将为大家详细讲解有关monitor temp db usage for MSSQL的示例代码，小编觉得挺实用的，因此分享给大家做个参考，希望大家阅读完这篇文章后可以有所收获。

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

关于“monitor temp db usage for MSSQL的示例代码”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，使各位可以学到更多知识，如果觉得文章不错，请把它分享出去让更多的人看到。