Alert when a specific SQL instance service has been started
One of the IT support team member asked me the question "Is there a way to have SQL Server 2005 send some sort of email notification when it starts up?"
The solution is straight forward and yes you can have such alerts or notifications based upon the facilities deployed on that SQL instance such as SQLMail or alerts using NET SEND operators. The option is to setup a scheduled job on that SQL instance SQLAgent to send a notification as an email, ensure that the SQLmail profile has been setup properly in order to send the notification. SP_PROCOPTION is useful for such instant execution of jobs whenever the SQL Server service is started. Make a note of extract from SQL Server Books online in this regard:
Use the scan for startup procs option to scan for automatic execution of stored procedures at Microsoft SQL Server startup time. If this option is set to 1, SQL Server scans for and runs all automatically run stored procedures defined on the server. The default value for scan for startup procs is 0 (do not scan).
The scan for startup procs option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change scan for startup procs only when show advanced options is set to 1. The setting takes effect after the server is restarted.
Also you can take help of third party tools to notify such events.