监控自查

原创

ggf6318 2010-08-21 00:10:39 博主文章分类：SQLServer ©著作权

文章标签 职场监控休闲自查 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者ggf6318的原创作品，如需转载，请与作者联系，否则将追究法律责任

说明

监控自查程序主要检查内容有：作业增删改明细、对象增删改明细、失败作业、性能计数、Trace分析是否正常

DLL触发器状态以及短信猫和hostmon程序是否运行；前面五项（作业增删改明细、对象增删改明细、失败作业、

性能计数、Trace分析是否正常）的监控都是根据79上DB_ALLHostInfo表中的hostname状态和每一项对应的表中

是否收集到了数据来做判断的；DLL触发器是根据79 DBCenter中的”usp_GetDLLTriggerState“存储过程获取的

（具体代码见ProcessState.sql 中的usp_GetDLLTriggerState），短信猫和hostmon程序的监控是通过运行79

DBCenter中的”ProcessState“存储过程获取的（具体代码见ProcessState.sql 中的ProcessState），最后通过

运行”usp_CheckServerState“发出邮件（这个Job部署在192.168.135.172上）。

--判断短信猫和hostmon程序是否正常
CREATE proc [dbo].[ProcessState]
as
begin
--declare @str=cmdlist varchar(5000)
create table #temp(cmdlist varchar(5000))

insert into #temp
exec master..xp_cmdshell 'tasklist /s 192.168.1.XX /U username /P xxxx /FO TABLE'

--短信猫判断短信猫的服务程序是否在进程中即可
if(select count(0) from #temp where cmdlist like 'SmsServer.exe%')>0
insert into ProcessRunState(processName,runState) values ('SmsServer.exe',1)
else
insert into ProcessRunState(processName,runState) values ('SmsServer.exe',0)

--hostmon
if(select count(0) from #temp where cmdlist like 'hostmon.exe%')>0
insert into ProcessRunState(processName,runState) values ('hostmon.exe',1)
else
insert into ProcessRunState(processName,runState) values ('hostmon.exe',0)

drop table #temp
end

--判断DLL触发器状态
CREATE proc [dbo].[usp_GetDLLTriggerState]
as
begin

update db_DLLTriggerState set states=3 where ImportTime<GETDATE()

select distinct hostname,DBName into #temp from db_ALLDBInfo a with(nolock) inner join DB_ALLHostInfo b with(nolock)
on a.SrvName=b.DBServerName
where DBName not in('master','model','msdb','tempdb','DBCenter','dts') and DBName not like '%snap%' and DBName not like '%test%' and DBName not like '%snp%' and DBName not like '%temp%' and DBName not like '%history%'
and isactive=1 and impdate>=GETDATE()-20
and hostname not in('192.168.1.XX','192.168.1.XXX')

declare @hostname varchar(100),@DBName varchar(100),@runsql varchar(500)
declare @count int
declare host cursor for
select * from #temp

create table #temp1 (hdbinfo varchar(200))

OPEN host
FETCH NEXT FROM host INTO @hostname, @DBName
WHILE(@@FETCH_STATUS=0)
BEGIN
    select @runsql=' insert into #temp1 select '''+@hostname+':'+@DBName+':'+'''+ name as hdbinfo from ['+@hostname+'].['+@DBName+']'+'.sys.triggers where parent_class_desc=''DATABASE''
and parent_class=0 and is_disabled=1 and exists (select 1 from ['+@hostname+'].[master].sys.databases where is_read_only=0 and name='''+@DBName+''' )'
    begin try
      exec (@runsql)
      if(select COUNT(0) from #temp1)>0
         insert into db_DLLTriggerState(HostDBInfo,States)
         select hdbinfo,0 from #temp1
      else
         insert into db_DLLTriggerState(HostDBInfo,States)
         select hdbinfo,1 from #temp1
    end try

    begin catch
        insert into db_DLLTriggerState(HostDBInfo,States)
        select @hostname+':'+@DBName,2
    end catch
    truncate table #temp1
FETCH NEXT FROM host INTO @hostname, @DBName
END

close host
deallocate host
drop table #temp1
drop table #temp
end

---每隔个小时运行一次，监控自查
CREATE proc [dbo].[usp_CheckServerState]
as
begin
declare @count int
declare @resultstr varchar(5000),@a varchar(200)
--作业增删改明细
select @count=count(hostname) from [192.168.1.XX].dbcenter.dbo.DB_ALLHostInfo with(nolock) where isactive=1 and DBServerName not in(
select distinct srvname from [192.168.1.XX].dbcenter.dbo.db_ALLjobs with(nolock) where impdate>=CONVERT(varchar(10),getdate(),21) and impdate<CONVERT(varchar(10),getdate()+1,21) )
and hostname not in('192.168.1.XX,'192.168.1.XXX')

set @resultstr='<tr><td>作业增删改明细</td><td>'
if(@count>0)
set @resultstr=@resultstr+
    stuff(
Replace(
Replace(
(select hostname from [192.168.1.XX].dbcenter.dbo.DB_ALLHostInfo with(nolock) where isactive=1 and DBServerName not in(
     select distinct srvname from [192.168.1.XX].dbcenter.dbo.db_ALLjobs with(nolock) where impdate>=CONVERT(varchar(10),getdate(),21) and impdate<CONVERT(varchar(10),getdate()+1,21) )
     and hostname not in('192.168.1.XX','192.168.1.XXX')
    for xml raw,elements
),'<row><hostname>',';')
,'</hostname></row>','')
,1,1,'')+'异常'
else
set @resultstr=@resultstr+'正常'

set @resultstr=@resultstr+'</td></tr><tr><td>对象增删改明细</td><td>'
--对象增删改明细
select @count=count(hostname) from [192.168.1.XX].dbcenter.dbo.DB_ALLHostInfo with(nolock) where isactive=1 and isgetobjects=1
and DBServerName not in(
select distinct servername from [192.168.1.XX].dbcenter.dbo.db_ALLobjects with(nolock) where impdate>=CONVERT(varchar(10),getdate(),21) and impdate<CONVERT(varchar(10),getdate()+1,21))
and hostname not in('192.168.1.XX','192.168.1.XXX')
if(@count>0)
set @resultstr=@resultstr+
    stuff(
Replace(
Replace(
(select hostname from [192.168.1.XX].dbcenter.dbo.DB_ALLHostInfo with(nolock) where isactive=1 and isgetobjects=1and DBServerName not in(
select distinct servername from [192.168.1.XX].dbcenter.dbo.db_ALLobjects with(nolock) where impdate>=CONVERT(varchar(10),getdate(),21) and impdate<CONVERT(varchar(10),getdate()+1,21))
and hostname not in('192.168.1.XX','192.168.1.XXX')
     for xml raw,elements
),'<row><hostname>',';')
,'</hostname></row>','')
,1,1,'')+'异常'
else
set @resultstr=@resultstr+'正常'
set @resultstr=@resultstr+'</td></tr><tr><td>失败作业</td><td>'

--失败作业
select @count=count(hostname) from [192.168.1.XX].dbcenter.dbo.DB_ALLHostInfo with(nolock) where isactive=1 and hostname not in(
select distinct replace(replace(Server,'[',''),']','') from [192.168.1.XX].dbcenter.dbo.db_info_ALLJobErrMsg with(nolock))
and hostname not in('192.168.1.XX','192.168.1.XXX')
if(@count>0)
    set @resultstr=@resultstr+ stuff(
Replace(
Replace(
( select hostname from [192.168.1.XX].dbcenter.dbo.DB_ALLHostInfo with(nolock) where isactive=1 and hostname not in(
select distinct replace(replace(Server,'[',''),']','') from [192.168.1.XX].dbcenter.dbo.db_info_ALLJobErrMsg with(nolock))
and hostname not in('192.168.1.XX','192.168.1.XXX')
     for xml raw,elements
),'<row><hostname>',';')
,'</hostname></row>','')
,1,1,'')+'异常'
else
    set @resultstr=@resultstr+'正常'
set @resultstr=@resultstr+'</td></tr><tr><td>性能计数</td><td>'

--性能计数
select @count=count(hostname) from [192.168.1.XX].dbcenter.dbo.DB_ALLHostInfo with(nolock) where isactive=1 and isGetPerformanceCounter=1 and hostname not in('192.168.1.XXX','192.168.1.XX')
and hostname not in(
select distinct hostname from [192.168.1.XX].dbcenter.dbo.db_ALLperformance with(nolock) where dtime>=CONVERT(varchar(10),getdate(),21) and dtime<CONVERT(varchar(10),getdate()+1,21))
if(@count>0)
set @resultstr=@resultstr+ stuff(
Replace(
Replace(
( select hostname from [192.168.1.XX].dbcenter.dbo.DB_ALLHostInfo with(nolock) where isactive=1 and isGetPerformanceCounter=1 and hostname not in('192.168.1.XXX','192.168.1.XX')
      and hostname not in(
      select distinct hostname from [192.168.1.XX].dbcenter.dbo.db_ALLperformance with(nolock) where dtime>=CONVERT(varchar(10),getdate(),21) and dtime<CONVERT(varchar(10),getdate()+1,21))
     for xml raw,elements
),'<row><hostname>',';')
,'</hostname></row>','')
,1,1,'')+'异常'
else
set @resultstr=@resultstr+'正常'

--检测DLL触发器状态
set @resultstr=@resultstr+'</td></tr><tr><td>DLL触发器状态</td><td>'
select @count=count(0) from [192.168.1.XX].dbcenter.dbo.db_DLLTriggerState with(nolock) where states=0 and importtime>getdate()-1
if(@count>0)
    set @resultstr=@resultstr+ stuff(
Replace(
Replace(
( select hostdbinfo from [192.168.1.XX].dbcenter.dbo.db_DLLTriggerState with(nolock) where states=0 and importtime>getdate()-1
     for xml raw,elements),'<row><hostdbinfo>',';'),'</hostdbinfo></row>','')
,1,1,'')+'未开启'
else
set @resultstr=@resultstr+'正常'

--检测Trace分析是否正常
set @resultstr=@resultstr+'</td></tr><tr><td>Trace分析</td><td>'
select @count=COUNT(0) from [192.168.1.XX].perfanalysis.dbo.queries_staticstics with(nolock)
where insdate > CONVERT(varchar(8),getdate(),112)
if(@count=0)
    set @resultstr=@resultstr+'异常'
else
    set @resultstr=@resultstr+'正常'

--判断短信猫和hostmon程序是否运行
set @resultstr=@resultstr+'</td></tr><tr><td>短信猫</td><td>'
select @count=datediff(MINUTE,MAX(imptdate),GETDATE()) from [192.168.1.XX].dbcenter.dbo.ProcessRunState with(nolock)
where runstate=1 and processname='SmsServer.exe'
if(@count>65)
    set @resultstr=@resultstr+'已关闭'
else
    set @resultstr=@resultstr+'正常'

set @resultstr=@resultstr+'</td></tr><tr><td>HostMon</td><td>'
select @count=datediff(MINUTE,MAX(imptdate),GETDATE()) from [192.168.1.XX].dbcenter.dbo.ProcessRunState with(nolock)
where runstate=1 and processname='hostmon.exe'

if(@count>65)
    set @resultstr=@resultstr+'已关闭'
else
    set @resultstr=@resultstr+'正常'

--
set @resultstr=@resultstr+'</td></tr>'

set @a='监控自查情况_'+convert (varchar(50),convert(varchar(5),YEAR(getdate()-1))+'年'+convert(varchar(2),month(getdate()))+'月'+convert(varchar(2),day(getdate())))+'日'+CONVERT(varchar(2), GETDATE(), 24)+'点'

set @resultstr='<html><H1>'+convert(varchar(5),YEAR(getdate()-1) )+'年'+convert(varchar(2),month(getdate()))+'月'+convert(varchar(2),day(getdate()))+'日'+CONVERT(varchar(2), GETDATE(), 24)+'点'+'监控自查情况</H1><body bgcolor=#E3F6CE><table border =1>'
    +'<tr><th>检测项</th><th>运行情况</th></tr>'+ @resultstr +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail
@recipients =N'XXX@XXX.com‘,
@body = @resultstr,
@body_format ='HTML',
@subject =@a,
@profile_name ='profile1'
end