Look for a word in Linux Directory

grep "deepak" *.sh

Email Notification for SQL Server Restart or Failover -- Part 2

Follow 

http://www.sqlservercentral.com/articles/Clustering/133902/ 

The restart or failover notification works by scheduling a job to run only when the SQL Agent starts that uses the code below.  I happen to like using HTML formatted e-mails to present results from queries.  I never know when I might have to forward that e-mail to someone at one of the companies that I work for so I have learned to make sure that results are easy to read.

-- create job that only runs when the SQL Agent starts that uses the code below.
-- replace all <<instancename>> & <<profile>> placeholders with correct values
SET NOCOUNT ON
DECLARE @body1 NVARCHAR(MAX)

-- instructions
SET @body1='<p> The <<instancename>> Instance has been restarted.</p>'
-- table attributes
SET @body1=@body1+'<table border="2" cellspacing="2" cellpadding="2">'
-- column headers
SET @body1=@body1+ '<tbody align="left" style="font-family:Arial; font-size: 11;" <TR><TH>Service Name</TH><TH>Startup Type</TH><TH>Status</TH>
                    <TH>Startup Time</TH></TR></tbody>'
-- data
SELECT @body1=@body1 +'<tbody align="left" style="font-family:Arial; font-size: 11;" <TR><TD>'+servicename+'</TD><TD>'+startup_type_desc+'</TD><TD>'+
                    status_desc+'</TD><TD>'+CAST(last_startup_time AS VARCHAR(30))+'</TD></TR></tbody>'
FROM sys.dm_server_services

-- Send an html formatted e-mail to notify of restart 
EXEC msdb.dbo.sp_send_dbmail
       @profile_name = '<<profile>>',  -- Use valid database mail profile here
       @recipients = 'DBA@company.com',  -- Use valid email address here
       @subject = 'SQL restart on <<instancename>>',
       @body = @body1,
       @body_format = 'HTML'

SET NOCOUNT OFF

Here is how to set up the job….

Add a job step with the code modified to have the correct instance name and a valid e-mail profile where needed.

Here is where the “magic” happens in that you can set the schedule to only run when the Agent starts.  With this schedule there is no need to run a job that polls data many times a day unnecessarily.

Here is an example of the stand-alone restart alert:

 

 A lot of people might worry that if the SQL Server Agent service only is restarted that you will also get the restart alert and that is a true statement.  However, you should investigate all unexpected service restarts.  You can see from the results above that both services restarted on the same day at virtually the same time which would indicate that there was a restart.  If only the Agent had been restarted, you would see that the startup time for the Agent would be very different than the SQL Server service startup time.  Either way, you would receive an alert of a service restart which is a good thing.

Cluster Failover Restart Notification

A SQL cluster failover is a bit different in that I can query the actual node name that SQL is active on by looking at the SERVERPROPERTY function.  (More on the SERVERPROPERTY function can be found here: https://msdn.microsoft.com/en-us/library/ms174396.aspx)  There is some prep that had to be done to place the current and previous node names into permanent tables that can be queried and compared when the Agent service is started.  This will allow us to detect if a failover or a restart has occurred.  You will need an administrative database available where the tables can be created.

-- Create tables
USE [ADMIN]
GO
CREATE TABLE [dbo].[CurrentNode](
       [cnode] [varchar](100) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[PreviousNode](
       [pnode] [varchar](100) NULL
) ON [PRIMARY]
GO

Once the tables are created they need to be populated with the current and previous node table values.  These will be the same at first so that after a failover then the comparison query will find that the previous node is not the same as the current node and the job will alert for a failover.  If the current node is the same as the previous node then a restart has occurred.

-- Prep - Put the same value into both tables so that if the current changes on failover a notice is sent
INSERT INTO CurrentNode (cnode)
 VALUES (CONVERT(VARCHAR(100),SERVERPROPERTY('ComputerNamePhysicalNetBIOS')))
INSERT INTO PreviousNode (pnode)
 VALUES (CONVERT(VARCHAR(100),SERVERPROPERTY('ComputerNamePhysicalNetBIOS')))

Here is the code that is used in the job that is set up just like the stand-alone restart notification job was done earlier.  Again, the logic below compares the values in the current node query to the pervious node table and will notify of either a failover or restart of the instance.  An e-mail can be sent to notify anyone who needs it of the failover or restart event. 

-- create job that only runs when the SQL Agent starts that uses the code below.
-- replace all <<instancename>> & <<profile>> placeholders with correct values
SET NOCOUNT ON
DECLARE @curnodename VARCHAR(100)
DECLARE @prevnodename VARCHAR(100)
DECLARE @body1 NVARCHAR(MAX)

SET @curnodename = CONVERT(VARCHAR(100),SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
SET @prevnodename = (SELECT pnode FROM PreviousNode)
IF @curnodename <> @prevnodename -- Failover
BEGIN
       UPDATE CurrentNode SET cnode = @curnodename

       -- instructions
       SET @body1='<p> A failover has occurred for the <<InstanceName>> cluster.</p>'
       -- table attributes
       SET @body1=@body1+'<table border="2" cellspacing="2" cellpadding="2">'
       -- column headers
       SET @body1=@body1+ '<tbody align="left" style="font-family:Arial; font-size: 11;" <TR><TH>Current Node</TH><TH>Previous Node</TH></TR></tbody>'

       -- data
       SET @body1=@body1 +'<tbody align="left" style="font-family:Arial; font-size: 11;" <TR><TD>'+@curnodename+'</TD><TD>'+@prevnodename+'</TD></TR></tbody>'
       -- Send an html formatted e-mail to notify of failover 
       EXEC msdb.dbo.sp_send_dbmail
             @profile_name = '<<profile>>',
             @recipients = 'DBA@company.com',
             @subject = 'Failover on <<instancename>> cluster',
             @body = @body1,
             @body_format = 'HTML';
       UPDATE PreviousNode SET pnode = @curnodename
END

IF @curnodename = @prevnodename  -- Restart
BEGIN
       -- Send an html formatted e-mail to notify of restart 
       EXEC msdb.dbo.sp_send_dbmail
             @profile_name = '<<profile>>',
             @recipients = 'DBA@company.com',
             @subject = 'Restart of <<InstanceName>> SQL Services',
             @body = 'The <<InstanceName>> cluster services may have been restarted but not failed over.';

END
SET NOCOUNT OFF

And finally here is the T-SQL of the actual job that I created for the cluster failover alert.  It will only run if the SQLAgent is started since we have set the job schedule to only run at that time.

-- Create job
USE [msdb]
GO
/****** Object:  Job [Failover Notification]    ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
  EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
  IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Failover Notification',
             @enabled=1,
             @notify_level_eventlog=0,
             @notify_level_email=2,
             @notify_level_netsend=0,
             @notify_level_page=0,
             @delete_level=0,
             @description=N'On restart of SQL Agent this job will run and notify if the instance has failed over.',
             @category_name=N'[Uncategorized (Local)]',
             @owner_login_name=N'sa',
             @notify_email_operator_name=N'DBA', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [FailoverCheck]    ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'FailoverCheck',
             @step_id=1,
             @cmdexec_success_code=0,
             @on_success_action=1,
             @on_success_step_id=0,
             @on_fail_action=2,
             @on_fail_step_id=0,
             @retry_attempts=0,
             @retry_interval=0,
             @os_run_priority=0, @subsystem=N'TSQL',
             @command=N'SET NOCOUNT ON

DECLARE @curnodename VARCHAR(100)
DECLARE @prevnodename VARCHAR(100)
DECLARE @body1 NVARCHAR(MAX)

SET @curnodename = CONVERT(VARCHAR(100),SERVERPROPERTY(''ComputerNamePhysicalNetBIOS''))
SET @prevnodename = (SELECT pnode FROM PreviousNode)

IF @curnodename <> @prevnodename -- Failover
BEGIN
       UPDATE CurrentNode SET cnode = @curnodename

       -- instructions
       SET @body1=''<p> A failover has occurred for the <<InstanceName>> cluster.</p>''
       -- table attributes
       SET @body1=@body1+''<table border="2" cellspacing="2" cellpadding="2">''
       -- column headers
       SET @body1=@body1+ ''<tbody align="left" style="font-family:Arial; font-size: 11;" <TR><TH>Current Node</TH><TH>Previous Node</TH></TR></tbody>''
       -- data
       SET @body1=@body1 +''<tbody align="left" style="font-family:Arial; font-size: 11;" <TR><TD>''+@curnodename+''</TD><TD>''+@prevnodename+''</TD></TR></tbody>''
       -- Send an html formatted e-mail to notify of failover 
       EXEC msdb.dbo.sp_send_dbmail
             @profile_name = ''<<profile>>'',
             @recipients = ''DBA@company.com'',
             @subject = ''Failover on <<InstanceName>> cluster'',
             @body = @body1,
             @body_format = ''HTML'';

       UPDATE PreviousNode SET pnode = @curnodename
END

IF @curnodename = @prevnodename  -- Restart
BEGIN
       -- Send an html formatted e-mail to notify of restart 
       EXEC msdb.dbo.sp_send_dbmail
             @profile_name = ''<<profile>>'',
             @recipients = ''DBA@company.com'',
             @subject = ''Restart of <<InstanceName>> SQL Services'',
             @body = ''The <<InstanceName>> cluster services may have been restarted but not failed over'';

END
SET NOCOUNT OFF',
             @database_name=N'ADMIN',
             @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'failover check sched',
             @enabled=1,
             @freq_type=64, -- this sets the job schedule to only run when the Agent starts
             @freq_interval=0,
             @freq_subday_type=0,
             @freq_subday_interval=0,
             @freq_relative_interval=0,
             @freq_recurrence_factor=0,
             @active_start_date=20150101,
             @active_end_date=99991231,
             @active_start_time=0,
             @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

The actual cluster failover notification looks like this:

 

Now when SQL restarts or a cluster failover happens, I receive a restart notification and can then research why it happened and troubleshoot if necessary.  I hope this is helpful to you.  Enjoy!

How to create a database mail account on MS SQL SERVER - Step 1 for email notification

I am currently working on SQL Server Always On Availability Group. I wanted to notified when my SQL Servers are failing over.

You can follow this link 

https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/create-a-database-mail-account?view=sql-server-2017

Create a Database Mail Account

• 03/14/2017
• 2 minutes to read
• Contributors
  • 
   
  • 

APPLIES TO: SQL Server Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse

Use either the Database Mail Configuration Wizard or Transact-SQL to create a Database Mail account.

• Before you begin: Prerequisites
• To Create a Database Mail Account, using: Database Mail Configuration Wizard, Transact-SQL
• Follow Up: Next Steps to Configure the Database Mail

Before You Begin

Prerequisites

• Determine the server name and port number for the Simple Mail Transfer Protocol (SMTP) server you use to send e-mail.If the SMTP server requires authentication, determine the user name and password for the SMTP server.
• Optionally, you may also specify the type of the server and the port number for the server. The server type is always 'SMTP' for outgoing mail. Most SMTP servers use port 25, the default.

Using Database Mail Configuration Wizard

To create a Database Mail account using a Wizard

• In Object Explorer, connect to the SQL Server instance you want to configure Database Mail on, and expand the server tree.
• Expand the Management node
• Double Click Database Mail to open the Database Mail Configuration Wizard.
• On the Select Configuration Task page, select Manage Database Mail accounts and profiles, and click Next.
• On the Manage Profiles and Accounts page, select Create a new account and click Next.
• On the New Account page, specify the account name, description, mail server information, and authentication type. Click Next
• On the Complete the Wizard page, review the actions to be performed and click Finishto complete creating the new account.

Using Transact-SQL

To Create a Database Mail account using Transact-SQL

Execute the stored procedure msdb.dbo.sysmail_add_account_sp to create the account and specify the following information:

• The name of the account to create.
• An optional description of the account.
• The e-mail address to show on outgoing e-mail messages.
• The display name to show on outgoing e-mail messages.
• The server name of the SMTP server.
• The user name to use to log on to the SMTP server, if the SMTP server requires authentication.
• The password to use to log on to the SMTP server, if the SMTP server requires authentication.
  The following example creates a new Database Mail account.

EXECUTE msdb.dbo.sysmail_add_account_sp  
    @account_name = 'AdventureWorks Administrator',  
    @description = 'Mail account for administrative e-mail.',  
    @email_address = 'dba@Adventure-Works.com',  
    @display_name = 'AdventureWorks Automated Mailer',  
    @mailserver_name = 'smtp.Adventure-Works.com' ;  

mlog$

--perceived size
select table_name,round((blocks*8),2)||'kb' "size"  from all_tables where table_name = 'MLOG$_MTL_ITEM_CATEGORIES1';

--actual

select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size" from all_tables where table_name ='MLOG$_MTL_ITEM_CATEGORIES1';


truncate table inv.MLOG$_MTL_ITEM_CATEGORIES1;

exec dbms_stats.gather_table_stats('INV','MLOG$_MTL_ITEM_CATEGORIES1');