~楓花雪岳~: [SQL] sp

星期五, 11月 09, 2012

[SQL] sp_procoption

sp_procoption 可以設定當啟動 SQL Server instance 時，會執行已經設定為自動執行的預存程序。

語法和應用

-- 語法
sp_procoption 
      [ @ProcName ]    -- 預存程序名稱
    , [ @OptionName ]  -- 只有一個設定值-startup
    , [ @OptionValue ] -- 設定值為 on（開啟）或 off（關閉）

-- 應用
-- 設定預存程序為自動執行
EXEC sp_procoption N'SPName' , 'startup' , 'on'
-- 取消預存程序的自動執行
EXEC sp_procoption N'SPName' , 'startup' , 'off'

-- 其他注意事項
-- 1. 自動啟動的預存程序必須在 master 資料庫內
-- 2. 預存程序不能包含 INPUT 或 OUTPUT 參數

scan for startup procs 伺服器組態

理論上要使用 sp_procoption 要先開啟 scan for startup procs 伺服器組態設定，但設定動作 sp_procoption 會自動進行，當將第一個預存程序設定自動執行時，scan for startup procs 會自動設定為值 1，當最後一個預存程序取消自動執行時，scan for startup procs 會自動設定為值 0，當然也可以在 SSMS 或利用 T-SQL 語法來設定。

EXEC sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE
GO
EXEC sp_configure 'scan for startup procs', 1 ; -- 啟動設為 1，取消設為 0
GO
RECONFIGURE
GO

利用 sp_procoption 搭配 Database Mail 來監控 SQL Server Instance 是否被重新開啟

-- Step 1：預存程序只能存在於 master 資料庫 
USE master
GO

-- Step 2：判斷欲建立的預存程序是否存在，存在的話則刪除預存程序
IF OBJECT_ID('[dbo].[uspSQLStart]') IS NOT NULL
  DROP PROC [dbo].[uspSQLStart]

-- Step 3：預存程序利用 msdb.dbo.sp_send_dbmail 來發信，當然前提是 Database Mail 已經設定好
CREATE PROC [dbo].[uspSQLStart]
AS
  DECLARE @Text varchar(512)
  
  SET @Text = 'The SQL Server [' + @@SERVERNAME + '].[' + @@SERVICENAME + '] ' +
              'restart at ' + CONVERT(char(19),GETDATE(),120) + '.'
  
  EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'Database Mail Profile' ,
      @recipients = '收件者Email' ,
      @subject = 'SQL Server 通知 - SQL Server 執行個體已經啟動' ,
      @body = @Text ,
      @body_format = 'TEXT'
GO
  
-- Step 4：最後利用 sp_procoption 把預存程序設為自動執行
EXEC sp_procoption N'uspSQLStart' , 'startup' , 'on'
GO

-- Step 5：重新啟動 SQL Server instance 就可以收到 Email 通知囉

查詢目前 SQL Server instance 啟動時，會自動執行的預存程序

SELECT
  ROUTINE_NAME , 
  ROUTINE_DEFINITION
FROM MASTER.INFORMATION_SCHEMA.Routines 
WHERE ROUTINE_TYPE = 'PROCEDURE'
  AND OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'ExecIsStartup') = 1