Archive for the ‘Alert on SQL Server Agent and SQL Server service restarts’ Category

How to get notified on SQL Server Agent and/or SQL Server Service restarts in addition to identifying the current active node for the failover cluster?

Unlike your TV, SQL Server can’t be turned off or on as per your desire. For mission critical SQL Servers, the uptime is extremely critical as one or more applications are connected to the databases to support the application users at all times throughout the day and possibly throughout many regions around the world. Planned maintenance downtime required for version upgrades, service pack upgrades, hot fixes, and production deployments or releases of which will require services or host reboots must be coordinated, planned, and approved by the management team. In case of unexpected service interruptions such as SQL Server Agent and SQL Server Service restarts and/or failover for the clusters, you must be notified and find out and document the root cause to prevent future surprises. How can we do that?

As we all know, when we schedule a SQL Server Agent job, one of the possible schedule types is “Start automatically when SQL Server Agent starts.” We can use this feature to notify us via database mail on SQL Server Agent and/or SQL Server Service restarts. When a failover on a cluster occurs, SQL Server service will also get restarted. We will identify the current active node for the failover clusters.

Here are the overview steps and concepts:

  1. Develop the T-SQL code to detect SQL Server Agent and/or SQL Server Service restarts in addition to finding the current active node in the cluster.
  2. Schedule a SQL Server Agent job to run the T-SQL code whenever SQL Server Agent starts.

Here are the implementation steps:

  1. Develop the T-SQL code to detect SQL Server Agent and/or SQL Server Service restarts in addition to finding the current active node in the cluster.
  2. -- Alert on SQL Server Agent and/or SQL Server service restarts
    -- Replace your recipients in line # 14
    
    -- declare the required variables
    DECLARE @Message VARCHAR(MAX)
    , @Subject VARCHAR(123)
    , @Recipients VARCHAR(123)
    , @IsDBMailEnabled BIT
    , @MailProfile VARCHAR(123)
    , @IsClustered VARCHAR(1) -- this is because SERVERPROPERTY('IsClustered') is a sql_variant data type
    , @CurrentNodeName VARCHAR(123)
    , @InstanceName VARCHAR(123)
    , @RestartTime VARCHAR(123)
    
    set @Recipients = 'email@company.com' -- specify the recipients here, separate with ; 
    
    SELECT @InstanceName = CONVERT(VARCHAR, SERVERPROPERTY('ServerName'))
    , @IsClustered = CONVERT (VARCHAR, SERVERPROPERTY('IsClustered'))
    , @CurrentNodeName = CONVERT (VARCHAR, SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
    
    -- SQL Server service has been restarted
    IF (
    	SELECT DATEDIFF(MI, CREATE_DATE,GETDATE() ) 
    	FROM SYS.DATABASES 
    	WHERE NAME = 'TEMPDB'
    	) <= 3 -- SQL Server service restarted in the past 3 minutes
    	BEGIN
    		SELECT @RestartTime = CONVERT(VARCHAR, create_date, 13)
    		FROM SYS.databases
    		WHERE NAME = 'TEMPDB'
    		
    		SET @Message = @InstanceName + ' SQL Server Agent service and SQL Server service have been restarted at: ' +@RestartTime
    		SET @Subject = @InstanceName + ' SQL Server Agent service and SQL Server service have been restarted!'
    
    		
    		IF @IsClustered = '1'
    			BEGIN
    				SET @Message = @Message + ' and the current active node is: ' +@CurrentNodeName+ '.'
    				SET @Subject = @Subject + ' The current active node is: ' +@CurrentNodeName+ '.' 
    			END
    	END
    ELSE 	
    	BEGIN
    		SET @Message = @InstanceName + ' SQL Server Agent service has been restarted!'
    		SET @Subject = @InstanceName + ' SQL Server Agent service restarted!'
    	END 
    
    SET @Message = @Message + CHAR(10)
    SET @Message = @Message + 'If this is not a planned maintenace. Please verify the status of the databases before restarting any application services and review the log files to find the possible root causes!'	
    
    -- Find the database mail profile name
    create table #dbmail_profile
    (
    profileid int null,
    profilename varchar(125) null,
    accountid int null,
    accountname varchar(125) null,
    sequencenumber int null
    )
    insert #dbmail_profile
    EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;
    
    select @MailProfile = profilename
    from #dbmail_profile
    where sequencenumber = 1
    
    drop table #dbmail_profile
    
    EXEC msdb.dbo.sp_send_dbmail
    	@profile_name = @MailProfile,
    	@recipients = @Recipients,
    	@subject = @Subject,
    	@body_format = 'TEXT',
    	@body = @Message;
    
  3. Schedule a SQL Server Agent job to run the T-SQL code whenever SQL Server Agent starts.
  4. Create a SQL Server Agent job the way as you have always been doing, then on the schedule page when choosing a “schedule type,” select the “Start automatically when SQL Server Agent starts” option.
    New Job Schedule

If you need to also identify the previous active node for the failover cluster in addition to the current active node, you will need to create a table in a “utility” database or the master database to store the cluster nodes information, then on SQL Server service restarts make a comparison of the previous active node and the current active node and embed the information on the notification email.

If you wish to check if other instances of SQL Server are running and you don’t have a third party monitoring system, you can schedule a SQL Server Agent job to ping the host of the other instances or check for their SQL Server Agent and SQL Server services statuses.

 

REFERENCES:

SERVERPROPERTY
http://msdn.microsoft.com/en-us/library/ms174396(v=sql.105).aspx

DATEDIFF
http://msdn.microsoft.com/en-us/library/ms189794(v=sql.105).aspx