SQLserver 邮件分发配置脚本

最新推荐文章于 2022-05-02 23:39:14 发布

翁特锋

最新推荐文章于 2022-05-02 23:39:14 发布

阅读量1.3k

点赞数

分类专栏： Ms SqlServer 文章标签： database sql server

本文链接：https://blog.csdn.net/wengtf/article/details/46787059

版权

Ms SqlServer 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

本文提供了一套用于配置 SQL Server 数据库监控的脚本，包括监控锁死、活动进程、开放事务等关键状态，并通过邮件将异常情况通知给操作员和 DBA。脚本详细介绍了如何发送邮件通知，以及如何根据不同的错误代码生成相应的通知内容。

摘要由CSDN通过智能技术生成

该脚本用于配置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