Technical Article

SQL Start up Script

,

I was working in a large 100+ SQL server web farm with many environments and clusters, and when a server restarted, I had no way of knowing where it was.

SO....

I wrote this script and created a job that would be scheduled to run at start up.  It would not only notify me via email that it started, but it would place the information in the application event log so that Microsoft System Center Operations Manager (SCOM) could see it. 

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[tblRegExtract]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
   DROP TABLE [dbo].[tblRegExtract]

CREATE TABLE dbo.tblRegExtract (
   sValue   [VARCHAR](45)  NULL,
   sData    [VARCHAR](100) NULL
   ) 
GO
                                                          /***********************
                                                          ** Declare supporting data structures
                                                         ***********************/DECLARE
   @Node    VARCHAR(500),
   @Cluster VARCHAR(100),
   @toEmail VARCHAR(500),
   @subject VARCHAR(500),
   @Body    VARCHAR(1000),
   @Start   VARCHAR(100)

SET @toEmail = 'name@domain.com'
                                                          /***********************
                                                          ** Get Registry AND server information
                                                         ***********************/BEGIN TRY
    IF CAST(LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),1) AS INT)>8
    BEGIN
        SET @Node = CAST(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS VARCHAR)
    END
    ELSE
    BEGIN
        INSERT INTO dbo.tblRegExtract
        EXEC master..xp_regread 'HKEY_LOCAL_Machine', 'SYSTEM\CurrentControlSet\Control\ComputerName\ComputerName\', 'ComputerName'
        SET  @Node =(SELECT sData  from dbo.tblRegExtract)
    END
END TRY
BEGIN CATCH
   SET  @Node ='n/a'
END CATCH

BEGIN TRY
   INSERT INTO dbo.tblRegExtract
   EXEC master..xp_regread 'HKEY_LOCAL_Machine', 'Cluster\', 'ClusterName'
   SET  @Cluster =ISNULL((SELECT sData from dbo.tblRegExtract),'n/a')
END TRY
BEGIN CATCH
   SET  @Cluster ='n/a'
END CATCH
                                                          /***********************
                                                          ** Build Email and Text.
                                                         ***********************/SET @subject = QUOTENAME(@Node) + ' has started.'


SET @Body = @subject + 'Please check cluster operations AND error logs for more detail.' + CHAR(13)+CHAR(10) + 
   '---------------------------------------------------------'+ CHAR(13)+CHAR(10) + 
   '   Cluster: ' + @Cluster + CHAR(13) + CHAR(10) +
   '      Node: ' + @Node + CHAR(13) + CHAR(10) +
   '    Server: ' + CAST(SERVERPROPERTY('ServerName') AS VARCHAR) + CHAR(13) + CHAR(10) +
   'Start Time: ' + CAST((SELECT TOP 1 create_date FROM sys.databases WHERE name='tempdb') AS VARCHAR) + CHAR(13) + CHAR(10) +
   '   Edition: ' + CAST(SERVERPROPERTY('Edition') AS VARCHAR) + CHAR(13) + CHAR(10) +
   '   Version: ' + CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR) + CHAR(13) + CHAR(10) +
   '    HotFix: ' + CAST(SERVERPROPERTY('ProductLevel')  AS VARCHAR) + CHAR(13) + CHAR(10) +  
    CHAR(13) + CHAR(10) +  CHAR(13) + CHAR(10) +
   '---------------------------------------------------------' +
    CHAR(13) + CHAR(10)
                                                          /***********************
                                                          ** Send Email
                                                         ***********************/EXEC  msdb.dbo.sp_send_dbmail
      @recipients=@toEmail,
      @subject = @subject,
      @Body = @Body,
      @body_format = 'TEXT';
                                                          /***********************
                                                          ** Write Information to Event Log so SCOM can see it..
                                                         ***********************/EXEC master..xp_logevent 55001, @Body
 
GO

Rate

4.86 (7)

You rated this post out of 5. Change rating

Share

Share

Rate

4.86 (7)

You rated this post out of 5. Change rating