We had a situation where Application owners automatically notified whenever server restarts. The SP_PROCOPTION do have a facility to perform this activity. The SP_PROCOPTION built-in stored procedure being used to invoke the user defined stored proc automatically.
The details of this implementation is as follows
- User defined meta data table to hold all Application Owner Details
- Create user defined Stored procedure Named “Notification” under MASTER database(Mandatory).The below automation developed with an intellegence to concatenate all Application Owner’s email-ids.
- Modify the stored procedure “Notification” – ProfileName and BlindCopyReceipents
- Add this stored procedure for automatic execution
- Once done Restart the SQL Instance .
Pre-requisites
- Mail Profile – Make sure that you have DB Mail Configured.
Download the complete code here SQL- AutoNotification to App Owners – When SQL Instance Restarts
Table Creation to store Meta Data about the Server
USE MASTER
GO
CREATE TABLE AutoAlert
(
id INT IDENTITY(1,1),
DbName VARCHAR(100),
DbOwner VARCHAR(100) DEFAULT ‘DBA Team’,
AppOwner VARCHAR(100),
AppOwnerID VARCHAR(100))
/* Insert AppOwners Details*/
INSERT INTO AutoAlert(dbname,AppOwner,AppOwnerID) VALUES(‘PROD_OLAP’,’Dave H’,’dheim@PowerSQL.com’)
INSERT INTO AutoAlert(dbname,AppOwner,AppOwnerID) VALUES(‘Crystal_App’,’Ana V’,’vana@PowerSQL.com’)
–SELECT * FROM dbo.AutoAlert
Stored Procedure Creation – Change Profile and BCC receipients
/* Create a new Stored Proc NOTIFICATION Under Master Database*/
CREATE PROCEDURE NOTIFICAION
AS
DECLARE @tableHTML NVARCHAR(MAX),
@td1 CHAR(15),
@td2 CHAR(15) ,
@td3 CHAR(20) ,
@td4 CHAR(15),
@td5 CHAR(15),
@td6 CHAR(15),
@td7 CHAR(15),
@Loopstatus2 INT,
@RowId2 INT,
@dmll NVARCHAR(max),
@dml2 NVARCHAR(max),
@Loopstatus1 INT,
@RowId1 INT,
@ProfileName VARCHAR(100),
@blind_copy_recipients VARCHAR(100),
@receipients VARCHAR(200),
@subject VARCHAR(500),
@email1 VARCHAR(MAX),
@email2 VARCHAR(MAX),
@email varCHAR(100)
/* Change Profile and Receipients details*/
SET @ProfileName =’PowerSQL’
SET @blind_copy_recipients=’PowerSQL@PowerSQL.com’
CREATE TABLE #mail
(
id INT IDENTITY(1,1),
email VARCHAR(100))
INSERT INTO #mail(email)
SELECT DISTINCT AppOwnerID FROM dbo.AutoAlert
SET @LoopStatus2=1
SET @RowId2=1
SET @email1=”
SET @email2=”
WHILE @LoopStatus2>0
BEGIN
SELECT @email=email FROM #mail WHERE id=@RowId2
IF @@ROWCOUNT = 0
BEGIN
SET @LoopStatus2=0
END
ELSE
BEGIN
SET @email1=@email1+@email+’;’
SET @email2=@email2+@email1
PRINT @email2
SET @email1=”
END
SET @RowId2=@RowId2+1
END
PRINT @email2
SET @email2=SUBSTRING(@email2,0,len(@email2))
PRINT @email2
SET @dmll=”
SET @dml2=”
SET @Loopstatus1=1
SET @RowId1=1
SET @tableHTML =
N'<H1>Server Restart Notification </H1>’ +
N'<table border=”1″ cellpadding=”1″><tr>
<th BGCOLOR=”RED”>[Server]</th>
<th BGCOLOR=”RED”>[DB Name]</th>
<th BGCOLOR=”RED”>[App Owner]</th>
</tr>’
WHILE @Loopstatus1<>0
BEGIN
SELECT
@td1 =@@servername,
@td2 =DBName,
@td3 =AppOwner
FROM AutoAlert WHERE id=@RowId1
IF @@ROWCOUNT=0
BEGIN
SET @Loopstatus1=0
END
ELSE
BEGIN
SET @dmll= @dml2+N'<tr><td>’+@td1+N'</td><td>’+@td2+'</td><td>’+@td3+'</td></tr>’
SET @dml2=@dmll
SET @dmll=”
END
SET @RowId1=@RowId1+1
END
SET @tableHTML=@tableHTML+@dml2+'</table>’+CHAR(13)+’Auto generated Email, Do not reply’+CHAR(13)+’Appvion DBA Team’
–PRINT @tableHTML
SET @Subject=’DB Server ‘+ @@servername +’ Restarted, Please Check all your dependent App Services’
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @ProfileName,
@recipients=@email2,
@blind_copy_recipients=@blind_copy_recipients,
@subject = @subject,
@body = @tableHTML,
@body_format = ‘HTML’;
DROP TABLE #mail
The following SQL sets a procedure for automatic execution
EXEC sp_procoption @ProcName = ‘NOTIFICAION’
, @OptionName = ‘startup’
, @OptionValue = ‘on’;
The following SQL stops a procedure from automatic execution
EXEC sp_procoption @ProcName = ‘NOTIFICAION’
, @OptionValue = ‘off’;
Restart SQL Instance
Output :-
You are so interesting! I don’t think I have read through a single thing like this before. So great to discover someone with original thoughts on this issue. Really.. thanks for starting this up. This site is one thing that is required on the web, someone with some originality!
LikeLike
Thank you….Happy Reading!!!!
LikeLike
Good post. I definitely appreciate this site. Thanks!
LikeLike
Thanks for reading my space.
LikeLike