這篇文章將為大家詳細講解有關monitor temp db usage for MSSQL的示例代碼，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。
創(chuàng)新互聯(lián)專注于企業(yè)全網(wǎng)營銷推廣、網(wǎng)站重做改版、七星網(wǎng)站定制設計、自適應品牌網(wǎng)站建設、html5、商城網(wǎng)站制作、集團公司官網(wǎng)建設、外貿(mào)營銷網(wǎng)站建設、高端網(wǎng)站制作、響應式網(wǎng)頁設計等建站業(yè)務，價格優(yōu)惠性價比高，為七星等各大城市提供網(wǎng)站開發(fā)制作服務。
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的示例代碼”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。
重慶分公司
重慶分公司
