SQLserver 邮件分发配置脚本

@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%loglock%errorfile%^>%ODBSERVER%dba%prdsrv%loglock%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%logopen_tran%errorfile%^>%ODBSERVER%dba%prdsrv%logopen_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,rtrim(convert(varchar,a.hostname)) as hostname,a.program_name)) as program_name,a.cmd)) as cmd,a.loginame)) as loginame,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,@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,-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),-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,?
? ? ? ? ?'database' ? ? = left(db_name(dbid),?
? ? ? ? ?'spid' ? ? ? ? = str(spid,4,0),??
? ? ? ? ?'block' ? ? ? ?= str(blocked,5,??
? ? ? ? ?'waittime' ? ? = waittime,?
? ? ? ? ?'phys_io' ? ? ?= str(physical_io,8,??
? ? ? ? ?'cpu(mm:ss)' ? = str((cpu/1000/60),6) + ':' + ??
? ? ? ? ? ? ? ? ? ? ? ? ? case ??
? ? ? ? ? ? ? ? ? ? ? ? ? ? when left((str(((cpu/1000) % 60),2)),1) = ' ' ??
? ? ? ? ? ? ? ? ? ? ? ? ? ? ? then stuff(str(((cpu/1000) % 60),2),'0') ??
? ? ? ? ? ? ? ? ? ? ? ? ? ? else str(((cpu/1000) % 60),2) ??
? ? ? ? ? ? ? ? ? ? ? ? ? ?END,?
? ? ? ? ?'mem(MB)' ? ? ?= str((convert(float,memusage) * 8192.0 / 1024.0 / 1024.0),?
? ? ? ? ?'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,??
? ? ? ? ?'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,? ?
? ? ? ?'hostname' ? ? = left(hostname,?
? ? ? ?'database' ? ? = left(db_name(dbid),?
? ? ? ?'spid' ? ? ? ? = str(spid,??
? ? ? ?'block' ? ? ? ?= str(blocked,??
? ? ? ?'waittime' ? ? = waittime,?
? ? ? ?'phys_io' ? ? ?= str(physical_io,??
? ? ? ?'cpu(mm:ss)' ? = str((cpu/1000/60),6) + ':' + ??
? ? ? ? ? ? ? ? ? ? ? ? case ?
? ? ? ? ? ? ? ? ? ? ? ? ? when left((str(((cpu/1000) % 60),1) = ' ' ??
? ? ? ? ? ? ? ? ? ? ? ? ? ? then stuff(str(((cpu/1000) % 60),'0') ?
? ? ? ? ? ? ? ? ? ? ? ? ? else str(((cpu/1000) % 60),2) ??
? ? ? ? ? ? ? ? ? ? ? ? END,?
? ? ? ?'mem(MB)' ? ? ?= str((convert(float,?
? ? ? ?'program_name' = left(program_name,??
? ? ? ?'command' ? ? ?= cmd,?
? ? ? ?'lastwaittype' = left(lastwaittype,?
? ? ? ?'login_time' ? = convert(char(19),??
? ? ? ?'last_batch' ? = convert(char(19),??
? ? ? ?'status' ? ? ? = left(status,?
? ? ? ?'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,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,-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),@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','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','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),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.',@print_mins) ? ? ? ? print '########' ? ? end ? ? print '' --dbcc traceoff(3004,-1) with NO_INFOMSGS select convert(varchar(20),getdate()) 'End Time' set nocount off

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

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