SQLserver Monitor( By Snapshot) Configuration

原创 2015年07月07日 11:17:51

该脚本用于配置sqlservere 数据库监控邮件分发:

@echo off
rem 
rem Purpose:          Send SQL Email thru SMTP to Computer Opeator and DBA 
rem Written By:       Open System and Database team
rem Date Created:     Dec 20, 2007
rem Date Modified:    Jan 14, 2008
rem Involved table:   perfmon.dbo.monjoblist
rem               :   perfmon.dbo.perfmonemail
rem Involved SP:      master.dbo.sp_send_cdosysmail
rem            :      perfmon.dbo.sp_sendemail
rem
rem #############################################################################
rem # if you need to modify the recipient's email list,                         #
rem # go to update the table perfmon.dbo.perfmonemail.tolist,                   #
rem # perfmon.dbo.perfmonemail.fromlist, perfmon.dbo.perfmonemail.cclist        #
rem #############################################################################

set ODBSERVER=DB_server_name

set prdsrv=%1
set errorcode=%2
set errorfile=%3
set FfTIME=%errorfile:~4,2%/%errorfile:~6,2%/%errorfile:~0,4% %errorfile:~-4,2%:%errorfile:~-2,2%
set emaildb=perfmon
set emailflag=0
set exitcode=0
SetLocal EnableDelayedExpansion


if "%errorcode%"=="1" set subject='[ALERT] Failed to ping %prdsrv%...'
if "%errorcode%"=="2" set subject='[ALERT] This is an Blocking on %prdsrv%...'
if "%errorcode%"=="3" set subject='[WARNING] This is an Uncommitted Long open transaction on %prdsrv%...'
if "%errorcode%"=="4" set subject='[ALERT] SQL Server %prdsrv% is failed to connect...'


if "%errorcode%"=="1" set body='^<font size="2" face="Arial"^>Failed to get the ping return on %prdsrv% at ^<b^>^<u^>%FfTIME%^</u^>^</b^>.  ^<p^>^<b^>Please ignore this email if it is under scheduled restart or maintenance.^</b^>^</p^> ^<b^>If this happened ^<u^>CONTINUOUSLY for 3 TIMES^</u^>^</b^>, Please refer to AIDC System Support List to call AIDC TS - Open ^& DB Systems team immediately.^</font^>'  
if "%errorcode%"=="2" set body='^<font size="2" face="Arial"^>Please go to: ^<p^>^<a href=\\%ODBSERVER%\dba\%prdsrv%\log\lock\%errorfile%^>\\%ODBSERVER%\dba\%prdsrv%\log\lock\%errorfile%^</a^>^</p^>to check the detail information^<p^>If you cannot find the log, please go to the ARCHIVE FOLDER to get back the log^<br^>^<a href=\\%ODBSERVER%\dba\%prdsrv%\archivelog\^>\\%ODBSERVER%\dba\%prdsrv%\archivelog\^</a^>^</p^>^</font^>'
if "%errorcode%"=="3" set body='^<font size="2" face="Arial"^>Please go to: ^<p^>^<a href=\\%ODBSERVER%\dba\%prdsrv%\log\open_tran\%errorfile%^>\\%ODBSERVER%\dba\%prdsrv%\log\open_tran\%errorfile%^</a^>^</p^>to check the detail information^<p^>If you cannot find the log, please go to the ARCHIVE FOLDER to get back the log^<br^>^<a href=\\%ODBSERVER%\dba\%prdsrv%\archivelog\^>\\%ODBSERVER%\dba\%prdsrv%\archivelog\^</a^>^</p^>^</font^>'
if "%errorcode%"=="4" set body='^<font size="2" face="Arial"^>Failed to execute ^<b^>^<u^>select @@servername^</u^>^</b^> on %prdsrv% at ^<b^>^<u^>%FfTIME%^</u^>^</b^>.  ^<p^>^<b^>Please ignore this email if it is under scheduled restart or maintenance.^</b^>^</p^> ^<b^>If this happened ^<u^>CONTINUOUSLY for 3 TIMES^</u^>^</b^>,try to connect the server console and login, call us if it does not work.^<p^>Please refer to AIDC System Support List to call AIDC TS - Open ^& DB Systems team immediately.^</p^>^</font^>'  

echo .
echo *** Sending email...  ***  
osql -S%ODBSERVER% -d%emaildb% -E -n -b -Q"sp_sendemail [%prdsrv%],%errorcode%,%subject%,%body%" 
if %errorlevel% neq 0 (set emailflag=1) else (set emailflag=0)
if "%emailflag%"=="0" echo *** Email has been sent. *** 
if "%emailflag%"=="1" echo *** Failed to send email.***  
if "%emailflag%"=="1" (goto error) else (goto noerror)


:error
set exitcode=1
goto quit


:noerror
set exitcode=0


:quit
EndLocal
exit /B %exitcode%

lock监控脚本:

--
-- Purpose:   SQL Server Blocking Check
-- Written By:   Open System and Database team
-- Date Created:   Nov 01, 2007
-- Date Modified:  
-- Description: Send email when there is blocking over the pre-set limit.
--

set nocount on
select convert(varchar(30),@@servername) 'DB Server Name'
select convert(varchar(20),getdate()) 'Start Time'
--dbcc traceon(3004, 3605, 3604, 3689, 4029, -1)


declare @flag int, @spid smallint,@blocked smallint, @waittime int, @hostname nchar(256), @program_name nchar(256), @cmd nchar(32), @loginame nchar(256), @login_time datetime,@last_batch datetime
declare @bhostname nchar(256), @bprogram_name nchar(256), @bcmd nchar(32), @bloginame nchar(256), @blogin_time datetime,@blast_batch datetime
declare @from varchar(30),@to varchar(30),@cc varchar(30),@subject varchar(100),@mailcontent varchar(255),@limit_mins int,@print_mins varchar(5)


-- The limitation check for sending email, the unit is minutes.
set @limit_mins=10


set @flag=0


declare logincursor cursor for
select a.spid,a.blocked,a.waittime,a.hostname,a.program_name,a.cmd,a.loginame,a.login_time,a.last_batch from master..sysprocesses a
where a.blocked <> 0 order by a.last_batch


print '#### Summary Information ####'
select a.spid,a.blocked,a.waittime,rtrim(convert(varchar,a.hostname)) as hostname,rtrim(convert(varchar,a.program_name)) as program_name,rtrim(convert(varchar,a.cmd)) as cmd,rtrim(convert(varchar,a.loginame)) as loginame,rtrim(convert(varchar,a.login_time)) as login_time,a.last_batch from master..sysprocesses a
where a.blocked <> 0 or a.open_tran <> 0 or a.status <> 'background' and a.spid > 10 and a.cmd not in('TASK MANAGER', 'CHECKPOINT SLEEP', 'AWAITING COMMAND')
order by a.last_batch
print '#### Summary Information ####'

open logincursor


fetch next from logincursor into @spid, @blocked, @waittime, @hostname, @program_name, @cmd, @loginame, @login_time, @last_batch


while @@fetch_status=0
  begin 
print '### Locked User Information ###'
print 'SPID Being Blocked (Suffering User): ' + convert(varchar,@spid)
print 'Login Name: ' + @loginame
print 'Login Time: ' + convert(varchar,@login_time)
print 'Last Batch execute Time: ' + convert(varchar,@last_batch)
print 'Lock waited Time: ' + convert(varchar,@waittime/1000/60) + ' Mins'
print 'Hostname: ' + @hostname
print 'Program Name: ' + @program_name
print 'Command: ' + @cmd
print 'SQL: ' execute('DBCC INPUTBUFFER('+@spid+')')
execute sp_lock @spid

print ''
print '###Locked User being Blocked by ###'
select @bhostname=hostname, @bprogram_name=program_name, @bcmd=cmd, @bloginame=loginame,
@blogin_time=login_time, @blast_batch=last_batch
from master..sysprocesses where spid=@blocked
print 'The SPID Blocked the above user: ' + convert(varchar,@blocked)
print 'Login Name: ' + @bloginame
print 'Login Time: ' + convert(varchar,@blogin_time)
print 'Last Batch execute Time: ' + convert(varchar,@blast_batch)
print 'Hostname: ' + @bhostname
print 'Program Name: ' + @bprogram_name
print 'Command: ' + @bcmd
print 'SQL: ' execute('DBCC INPUTBUFFER('+@blocked+')')


execute sp_lock @blocked

if (@waittime/1000/60) > @limit_mins
  begin
    set @flag = 1
  end

print ''
print '######################################################################'
print '######################################################################'
print ''
    fetch next from logincursor into @spid, @blocked, @waittime, @hostname, @program_name, @cmd, @loginame, @login_time, @last_batch
  end

quit:
close logincursor
deallocate logincursor


    if @flag = 1
    begin
        set @print_mins=convert(varchar(5),@limit_mins)
        print ''
        print '########'
        RAISERROR ('This is an Blocking on %s for over %s mins', 16, 1,@@servername, @print_mins)
        print '########'
    end


--dbcc traceoff(3004, 3605, 3604, 3689, 4029, -1)
select convert(varchar(20),getdate()) 'End Time'
set nocount off

sp_who 监控脚本:

--
-- Purpose:   SQL Server current process check
-- Written By:   Open System and Database team
-- Date Created:   Nov 01, 2007
-- Date Modified:  
--

set nocount on
select convert(varchar(30),@@servername) 'DB Server Name'
select convert(varchar(20),getdate()) 'Start Time'
--dbcc traceon(3004, 3605, 3604, 3689, 4029, -1)


exec('sp_who2')
print ''
--print '##################'
--print ''
--exec('sp_who')


print '##################'
print ''


declare @handle binary(20),   
        @spid   smallint,  
        @rowcnt smallint,  
        @output varchar(500),  
        @blocks int,  
        @spids  int  
  
declare ActiveSpids CURSOR FOR  
select sql_handle, spid  
  from sysprocesses   
 where sql_handle <> 0x0000000000000000000000000000000000000000  
   and spid <> @@SPID  
order by cpu desc  
  
OPEN ActiveSpids  
FETCH NEXT FROM ActiveSpids  
INTO @handle,  
     @spid  
  
  
set @rowcnt = @@CURSOR_ROWS  
  
print '===================='  
print '= CURRENT ACTIVITY ='  
print '===================='  
print convert(char(19),getdate(),120)  
print ' '  
print 'Active  SPIDs: ' + convert(varchar(8),@rowcnt)  
  
-- Blocking processes summary  
select @blocks = count(*) from master..sysprocesses where blocked > 0  
print 'Blocked SPIDs: ' + convert(varchar(8),@blocks)  
  
select @spids = count(*) from master..sysprocesses   
print 'Total   SPIDs: ' + convert(varchar(8),@spids)  
  
  
IF (@blocks > 0)  
BEGIN  
  print ' '  
  print ' '  
  print 'Blocked Process Summary'  
  print '-----------------------'  
  print ' '  
  select 'loginame'     = left(loginame, 30),    
         'hostname'     = left(hostname,30),  
         'database'     = left(db_name(dbid),30),  
         'spid'         = str(spid,4,0),   
         'block'        = str(blocked,5,0),   
         'waittime'     = waittime,  
         'phys_io'      = str(physical_io,8,0),   
         'cpu(mm:ss)'   = str((cpu/1000/60),6) + ':' +   
                          case   
                            when left((str(((cpu/1000) % 60),2)),1) = ' '   
                              then stuff(str(((cpu/1000) % 60),2),1,1,'0')   
                            else str(((cpu/1000) % 60),2)   
                           END ,  
         'mem(MB)'      = str((convert(float,memusage) * 8192.0 / 1024.0 / 1024.0),8,2),  
         'program_name' = left(program_name,50),   
         'command'      = cmd,  
         'lastwaittype' = left(lastwaittype,15),  
         'login_time'   = convert(char(19),login_time,120),   
         'last_batch'   = convert(char(19),last_batch,120),   
         'status'       = left(status, 10),  
         'nt_username'  = left(nt_username,20)  
    from master..sysprocesses  
    where blocked > 0  
    print ' '  
    print ' '  
END  
   
WHILE (@@FETCH_STATUS = 0)  
BEGIN  
  print ' '  
  print ' '  
  print 'O' + replicate('x',120) + 'O'  
  print 'O' + replicate('x',120) + 'O'  
  print ' '  
  print ' '  
  print ' '  
  
select 'loginame'     = left(loginame, 30),    
       'hostname'     = left(hostname,30),  
       'database'     = left(db_name(dbid),30),  
       'spid'         = str(spid,4,0),   
       'block'        = str(blocked,5,0),   
       'waittime'     = waittime,  
       'phys_io'      = str(physical_io,8,0),   
       'cpu(mm:ss)'   = str((cpu/1000/60),6) + ':' +   
                        case  
                          when left((str(((cpu/1000) % 60),2)),1) = ' '   
                            then stuff(str(((cpu/1000) % 60),2),1,1,'0')  
                          else str(((cpu/1000) % 60),2)   
                        END ,  
       'mem(MB)'      = str((convert(float,memusage) * 8192.0 / 1024.0 / 1024.0),8,2),  
       'program_name' = left(program_name,50),   
       'command'      = cmd,  
       'lastwaittype' = left(lastwaittype,15),  
       'login_time'   = convert(char(19),login_time,120),   
       'last_batch'   = convert(char(19),last_batch,120),   
       'status'       = left(status, 10),  
       'nt_username'  = left(nt_username,20)  
  from master..sysprocesses  
  where spid = @spid  
  print ' '  
  print ' '  
    
  -- Dump the inputbuffer to get an idea of what the spid is doing  
  dbcc inputbuffer(@spid)  
  print ' '  
  print ' '  
  
  -- Use the built-in function to show the exact SQL that the spid is running  
  select * from ::fn_get_sql(@handle)  
    
  FETCH NEXT FROM ActiveSpids  
  INTO @handle,  
       @spid  
END  
close ActiveSpids  
deallocate ActiveSpids  

--dbcc traceoff(3004, 3605, 3604, 3689, 4029, -1)
select convert(varchar(20),getdate()) 'End Time'
set nocount off

Open_transcation 脚本:

set nocount on
select convert(varchar(30),@@servername) 'DB Server Name'
select convert(varchar(20),getdate()) 'Start Time'
--dbcc traceon(3004, 3605, 3604, 3689, 4029, -1) with NO_INFOMSGS


declare @dbname varchar(255),@info varchar(255), @content varchar(255), @printout varchar(255), @flag int,@timediff int, @allstatopts int,@alloptopts int,@curstatus int,@chkstatus int


declare @from varchar(30),@to varchar(30),@cc varchar(30),@subject varchar(100),@mailcontent varchar(255),@limit_mins int,@print_mins varchar(5)


-- The limitation check for sending email, the unit is minutes.
set @limit_mins=10


select @allstatopts=number from master.dbo.spt_values where type = 'D' and name = 'ALL SETTABLE OPTIONS'
select @alloptopts=number from master.dbo.spt_values where type = 'D2' and name = 'ALL SETTABLE OPTIONS'

set @flag=0


declare dbcursor cursor for
select name from master..sysdatabases

open dbcursor

fetch next from dbcursor into @dbname

while @@fetch_status=0
  begin 

-- Check the database option
    select v.name as 'The following dboption has been set:',v.number,v.type into #dboption
    from master.dbo.spt_values v, master.dbo.sysdatabases d
    where d.name=@dbname and (
    (v.number & @allstatopts <> 0
    and v.number not in (-1,@allstatopts)
    and v.type = 'D'
    and (v.number & d.status)=v.number) or 
    (v.number & @alloptopts <> 0
    and v.number not in (-1,@alloptopts)
    and v.type = 'D2'
    and (v.number & d.status2)=v.number)
    )
    select @curstatus=status from master.dbo.sysdatabases where name=@dbname
    select @chkstatus=sum(number) from #dboption where type='D'


   if ((@curstatus - @chkstatus)) > 0
     begin
         insert into #dboption select name,number,type from master.dbo.spt_values where type = 'D' and number=(@curstatus - @chkstatus) and name <> 'cleanly shutdown'
     end


-- Exclude those dboption in 'loading','pre recovery','recovering','not recovered','single user'
    if exists (select 1 from #dboption where [The following dboption has been set:] in ('loading','pre recovery','recovering','not recovered','single user'))
    begin
       select [The following dboption has been set:] as '!!!This database is under the dboption:' from #dboption where [The following dboption has been set:] in ('loading','pre recovery','recovering','not recovered','single user')
       print 'so this database is being ignored.'
    end
    else
    begin
        create table #temp (info varchar(255), content varchar(255))
        insert into #temp exec('dbcc opentran(['+ @dbname + ']) with TABLERESULTS,NO_INFOMSGS')
        insert into #temp (info, content) select 'Current Time', convert(varchar(20),getdate())

-- if open transaction found, set its run time to @timediff. 
-- else set @timediff to zero
if exists (select 1 from #temp where info='OLDACT_STARTTIME')
 begin 

select @timediff=datediff(mi,convert(datetime,[content],109),convert(datetime,getdate(),109))
from #temp 
where info='OLDACT_STARTTIME'
--print 'This open transaction has last for: ' + convert(varchar,@timediff) + ' mins'
 end
else 
set @timediff=0


if @timediff > @limit_mins
begin
set @flag = 1


print '##############################'
print 'Checking database - ' + @dbname
print ''
-- to show db options, uncomment following line
--select [The following dboption has been set:] from #dboption
print 'Oldest active transaction:'
declare otcursor cursor for
select info = 
case info
  WHEN 'OLDACT_SPID' THEN 'SPID (server process ID)'
  WHEN 'OLDACT_UID' THEN 'UID (user ID)'
  WHEN 'OLDACT_NAME' THEN 'Transaction Name'
  WHEN 'OLDACT_LSN' THEN 'LSN'
  WHEN 'OLDACT_STARTTIME' THEN 'Start time'
  ELSE info
END,
content
from #temp


open otcursor
fetch next from otcursor into @info,@content
while @@fetch_status=0
 begin
if @info='SPID (server process ID)'
 begin
select @printout=loginame from sysprocesses where spid=@content
print 'Login Name: ' + @printout
print 'SPID: ' + @content
print 'SQL: ' execute('DBCC INPUTBUFFER('+@content+')')
 end
else
 begin
print @info + ': ' + @content
 end

fetch next from otcursor into @info,@content
 end
   
close otcursor
deallocate otcursor

end
        drop table #temp
    end
    
print ''    
    drop table #dboption 
    fetch next from dbcursor into @dbname
  end

close dbcursor
deallocate dbcursor


    if @flag = 1
    begin
        set @print_mins=convert(varchar(5),@limit_mins)
        print ''
        print '########'
        RAISERROR ('This is an open transaction for over %s mins.', 16, 1, @print_mins)
        print '########'
    end


    print ''
--dbcc traceoff(3004, 3605, 3604, 3689, 4029, -1) with NO_INFOMSGS
select convert(varchar(20),getdate()) 'End Time'
set nocount off


相关文章推荐

RpcMonitorFilter——monitor dubbo by filter

package com.qunar.flight.pangolin.pay.filter;import com.alibaba.dubbo.common.Constants; import com.a...

Visual Studio 2013开发 mini-filter driver step by step 获取可执行文件名称 - 实现process monitor的一个功能 (10)

国庆就要结束了,遗憾一下:)

Linux 配置收集 - Some Configuration In Linux - By黑月君

记录黑月一些在Linux使用时用到的配置 博客开篇一,在这里玩玩看看 喜欢折腾和装酷所以安装了Linux,遇到很多需要自定义配置的情况,此记 暂时主要用于Ubuntu还有Elementary OS(...

pom.xml报错Plugin execution not covered by lifecycle configuration

错误如下: Plugin execution not covered by lifecycle configuration: org.apache.maven.plugins:maven-jar-p...

MyEclipse2014创建Maven项目pom.xml文件报Plugin execution not covered by lifecycle configuration

用myeclipse 2014 整maven 项目时,pom.xml文件中报以下两个异常: Multiple annotations found at this line: - Plugin exec...

Plugin execution not covered by lifecycle configuration: org.codehaus.mojo:aspectj-maven-plugin

解决方案1: 加入如下代码:
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQLserver Monitor( By Snapshot) Configuration
举报原因:
原因补充:

(最多只允许输入30个字)