Hello it’s your server calling

This is nothing exciting but I've always found this startup procedure  very useful.
All this simple procedure does is send you an email if the SQL Service Starts.
If your Server is a cluster it will tell you which node you're on.
--
On it's own this procedure can't actually be used as I route the output through another procedure, dbasp_SendMessage, this procedure routes a passed message to either a smtp email or a log table or both, the destination is set in a server config table which allows me to still log dba messages even if the mail server is unavailable or if I'm in a environment which doesn't have email.
I'm assuming that most have a favourite stored procedure which will send an smtp email so all you need to do is substitute the procedure.
For SQL 2005 you'll have to define and assign the variables on seperate lines.
--
All my SQL Jobs have customised failure/success alerting which is routed through dbasp_SendMessage, the dbasp_ prefix was accepted practice at one client and I have not chosen to change it.
--
I set all my alerts with the format of three exclamation marks either side of a key word, this assists with rules in Outlook, by default the sender address of the email is set to @@ServerName, this avoids any confusion with multiple environments raising alerts.

 

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_Mail_DBA_on_Start]    Script Date: 11/21/2011 13:53:26 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Mail_DBA_on_Start]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Mail_DBA_on_Start]
GO

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[sp_Mail_DBA_on_Start]    Script Date: 11/21/2011 13:53:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE procedure [dbo].[sp_Mail_DBA_on_Start]
-- ============================================================================
--  Stored Procedure:     sp_Mail_DBA_on_Start                          
--                       
--  Written by:     Colin Leversuch-Roberts
--                     www.kelemconsulting.co.uk 
--                    (c) january 2005                            
--                                                                
--  Purpose:          Nothing very complicated, uses  SMTP proc to mail the DBA group
--                    when the sql server restarts
--                    Master procs can be set to run on start-up, this is supported by Microsoft
--                    and is a valid implementation.
--                    set to autorun using exec sys.sp_procoption '[dbo].[sp_Mail_DBA_on_Start]','startup','true';
--                    check status using select OBJECTPROPERTYEX(  object_id('[dbo].[sp_Mail_DBA_on_Start]'),'ExecIsStartup');                            
--                                                                
--  System:            master database
--                    does not need to be marked as a system object
--
--  Input Paramters:       none
--                       
--  Output Parameters:     None                                      
--                                                                
--  Usage:                 runs on sql server service restart                                          
--                  
--  Calls:                 dbo.dbasp_SendMessage
--                                                           
--  Data Modifications: None                             
--                                                                
--  VERSION HISTORY
--  Version No        Date            Description
--  1            28-January-2005        Initial Release 
--    2           5 dec 2008            now sends message through  dbasp_SendMessage
--    3            jan 2011            changed to pick up physical name so names cluster node in message
-- ============================================================================
as
set nocount on;
declare @subject varchar(100) = '!!! ALERT !!! The SQL Service has just started';
declare @message varchar(1000) = 'The SQL Service has started on '+convert(varchar(100),SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))+' - Time of event '+convert(varchar(30),getdate())+''+char(10)+char(10)+' If this was not planned then there may have been a server os failure or unauthorised reboot';
declare @to varchar(255) = 'DBAGroup@yourdomain.com';
exec ServerAdmin.dbo.dbasp_SendMessage @subject,@message,@to;
GO

EXEC sp_procoption N'[dbo].[sp_Mail_DBA_on_Start]', 'startup', '1'
GO
Published 21 November 2011 20:12 by GrumpyOldDBA

Comments

No Comments