資料庫運行過程中，有時會發生不可預期的狀況，需要 DBA 作即時的處理；此時便可利用 SQL Server 所提供的 Database Mail Server 來傳送 Email 訊息給相關維修人員。不幸的，SQL Server 2008 R2 Express 的 Mangement Studio 並不提供此種設定的介面。因此，本人在此將介紹一解決方案，以自行開發的程式來達到相同的效果。共可分為 4 部分 --
- Check_DB_Size.bat ：DOS Batch 檔案，透過 sqlmd 呼叫 Stored Procedure –> usp_DB_Size_Checker 以進行資料庫實體檔案大小檢查作業。
- usp_DB_Size_Checker： SQL Server Stored Procedure，存在於欲監視的資料庫中，負責檢查該資料庫實體檔案大小；當超過警示值時，便呼叫另一 Stored Procedure --> usp_Alert_Sender 進行警示 Email 傳送作業。
- usp_Alert_Sender：負責傳送 Email (此處呼叫 gmail 進行訊息的傳送)。
- 工作排程器：Windows 所內附功能，負責定時呼叫 Check_DB_Size.bat (以 Windows 7 進行說明)。
建置步驟
1) 在安裝 SQL Server 2008 R2 Express 的電腦上，確定目錄 C:\Home\Expense 已經存在。
2) 用文章 (SQL Server 2008 R2 Express 自動化管理 (一) 定期備份) 建立的 Expense 資料庫、DETAIL_RECORD Table。
3) 建立一個供排程作業使用的 Check_DB_Size.bat DOS Batch 檔案，並將其存放到 C:\Home\Expense 目錄。內容如下：
@echo off sqlcmd -S localhost\SQLEXPRESS -E -d Expense -Q "exec usp_DB_Size_Checker"
4) 撰寫 usp_Alert_Sender Stored Procedure，並於 SQL Server 的 Expense 資料庫中編譯 (Compile)；此處假設由 Gmail 的 xxx@gmail.com 帳號送出警示 Email 給 Hotmail 的 yyy@hotmail.com 帳號。
use Expense;
if object_id ('usp_Alert_Sender') is not null
drop proc usp_Alert_Sender
go
Create Proc usp_Alert_Sender @prm_database_name varchar(200)
as
declare @acnt_id int,
@msg_1 varchar(50),
@msg_2 varchar(50),
@prof_id int
declare @db_mail_server_status varchar(20),
@sql nvarchar(2000),
@prof_name sysname,
@acnt_name sysname
declare @tbl_db_mail_server table (d_status varchar(20))
set nocount on
-- 初始化 ---------------------------------------------
select @prof_name = 'DB Administrator Profile'
select @acnt_name = 'DBA_1'
------------------------------------------------------
-- 啟動 DB Mail Server --------------------------------
exec sp_configure 'show advanced options', 1
reconfigure
exec sp_configure 'Database Mail XPs', 1
reconfigure
exec msdb.dbo.sysmail_start_sp;
------------------------------------------------------
-- 檢查 Account 是否已存在 -----------------------------
-- 若不存在, 則建立新的 Account 。
if not exists(select * from msdb.dbo.sysmail_account where name = @acnt_name)
exec msdb.dbo.sysmail_add_account_sp
@account_name = @acnt_name,
@description = 'Alert Message Account',
@email_address = 'xxx@gmail.com',
@replyto_address = 'xxx@gmail.com',
@display_name = 'DB Alert',
@mailserver_name = 'smtp.gmail.com',
@port = 587,
@username = 'xxx@gmail.com',
@password = 'Your Gmail Password',
@enable_ssl = 1
-- 取得 Profile 的 ID 值
select @prof_id = profile_id from msdb.dbo.sysmail_profile
------------------------------------------------------
-- 檢查 Profile 是否已存在 -----------------------------
-- 若不存在, 則建立新的 Profile 。
if not exists(select * from msdb.dbo.sysmail_profile where name = @prof_name)
exec msdb.dbo.sysmail_add_profile_sp @profile_name = @prof_name
-- 取得 Account 的 ID 值
select @acnt_id = account_id from msdb.dbo.sysmail_account
------------------------------------------------------
-- 檢查該 Account 是否已被加入此 Profile ----------------
-- 若尚未加入, 則將其加入 。
if not exists(select pa.*
from msdb.dbo.sysmail_profileaccount pa,
msdb.dbo.sysmail_profile p,
msdb.dbo.sysmail_account a
where pa.profile_id = p.profile_id
and pa.account_id = a.account_id)
begin
exec msdb.dbo.sysmail_add_profileaccount_sp
@profile_id = @prof_id,
@account_id = @acnt_id,
@sequence_number = 1
-- 授權 DBMailUsers 可使用此 Profile
exec msdb.dbo.sysmail_add_principalprofile_sp
@profile_id = @prof_id,
@principal_name = 'public',
@is_default = 1
end
------------------------------------------------------
-- 傳送 Alert Email -----------------------------------
select @msg_1 = @prm_database_name + ' Database is about to exceed 10 GB !'
select @msg_2 = 'SQL Server: ' + @prm_database_name + ' DB Warning'
exec msdb.dbo.sp_send_dbmail
@recipients = 'yyy@hotmail.com',
@body = @msg_1,
@subject = @msg_2,
@profile_name = @prof_name
------------------------------------------------------
set nocount off
return
5) 撰寫 usp_DB_Size_Checker Stored Procedure，並於 SQL Server 的 Expense 資料庫中編譯 (Compile)，內容如下：
use Expense;
if object_id ('usp_DB_Size_Checker') is not null
drop proc usp_DB_Size_Checker
go
Create Proc usp_DB_Size_Checker
as
declare @alert_boundary int,
@size int,
@size_string varchar(30)
declare @tbl_db_info table (
t_name varchar(200),
t_size varchar(20),
t_owner varchar(50),
t_dbid int,
t_created datetime,
t_status varchar(4000),
t_compat int)
set nocount on
-- 設定資料庫檔案大小警戒值為 10 GB * 90%
select @alert_boundary = 10 * 1000000 * 0.9 -- 單位：MB
-- 找出 Expense 目前的檔案大小 ------------------------
insert into @tbl_db_info exec sp_helpdb
select @size_string = t_size
from @tbl_db_info
where t_name = 'Expense'
select @size_string = t_size
from @tbl_db_info
where t_name = 'Expense'
select @size = cast(replace(@size_string, ' MB', '') as real)
----------------------------------------------------
-- 當資料庫檔案大小 >= 10 GB * 90% 時, 呼叫 usp_Alert_Sender 發出通知。
if @size >= @alert_boundary
exec usp_Alert_Sender 'Expense'
set nocount off
return
6) 將工作排程器設定為每天下午 06:00:00 進行資料庫大小檢查作業：(螢幕左下方) 開始 --> 所有程式 --> 附屬應用程式 --> 系統工具 --> 工作排程器
(1). 於 "工作排程器" 視窗中，highlight 畫面左方的 "工作排程器程式庫"後，按一下滑鼠右鍵，選取 "建立工作"。
(2). 此時將出現 "建立工作" 視窗 (目前位於 "一般" 頁籤)。
(3). 在 "名稱" 欄位輸入 DB Size Check，勾選 "只有使用者登入時才執行"、"以最高權限執行" 欄位後，點選上方 "觸發程序" 頁籤繼續設定。
(4). 於 "觸發程序" 頁籤點選 "新增"，出現 "新增觸發程序" 視窗 --
- 選擇 "每天"。
- "開始" 欄位，選取 2012/4/1 下午 06:00:00 。
- "每隔" 欄位，選取 "1" 天。
- 按下 "確定" 功能鍵後，點選上方 "動作" 頁籤繼續設定。
- "執行" 欄位，選取 "啟動程式"。
- 按下 "瀏覽"，於出現的視窗中，選擇 C:\Home\Expense\Check_DB_Size.bat，按 "開啟舊檔"。
- 按下 "確定" 功能鍵後，再按一次 "確定"。
