Send alert out when SQL Server Restarts

An easy to set up procedure which runs every time SQL Server starts. This can be particular useful in clusters if the instances can move around nodes.

CREATE PROCEDURE [dbo].[spEmailSQLServerRestart]
AS
  BEGIN
-- Declare Variables
DECLARE @strServer VARCHAR(128) = CONVERT(VARCHAR(128), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'))
DECLARE @strMailSubject VARCHAR(128) = 'SQL Server '
+ UPPER(@@SERVERNAME) + ' restarted!'
DECLARE @strMailBody VARCHAR(1000) = 'SQL Server '
+ UPPER(@@SERVERNAME) + ' restarted at '
+ CONVERT(VARCHAR(12), GETDATE(), 108) + ' on '
+ CONVERT(VARCHAR(12), GETDATE(), 103)
+ ' Now running on server: ' + @strServer
-- Wait for the database mail engine to start
WAITFOR DELAY '00:00:15'

DECLARE @sender varchar (50)
SELECT @sender = name from msdb..sysmail_profile where profile_id = 1

-- Send Email
EXEC msdb.dbo.sp_send_dbmail @profile_name = @sender,
@recipients = 'DBAOnCallAlerts@autotrader.co.uk',
@subject = @strMailSubject, @body = @strMailBody,
@body_format = 'HTML';
END

Just deploying the procedure in this scenario is not enough to trigger the alert. We now need to tell SQL Server that it should scan for this procedure every time in starts up. This is known as auto execution in SQL Server and is set by sp_procoption (from SQL Server 2008)

To Set a procedure to run on instance startup, you can run:

-- Sets stored procedure for automatic execution.
sp_procoption    @ProcName = 'spEmailSQLServerRestart',
@OptionName = 'startup',
@OptionValue = 'on'

To see which procedures have the startup option set:

SELECT name, type_desc, create_date, modify_date
FROM sys.procedures
WHERE is_auto_executed = 1

From Microsoft:

Startup procedures must be in the master database and cannot contain INPUT or OUTPUT parameters. Execution of the stored procedures starts when all databases are recovered and the “Recovery is completed” message is logged at startup.

 

ADRCI error – DIA-48448: This command does not support multiple ADR homes

Problem: When running adrci on an oracle server with multiple homes, you can get the following error when trying to complete some actions:

DIA-48448: This command does not support multiple ADR homes

Reason is existence of multiple oracle database instances/ASM/Listener homes etc. Solution is to set the ADRCI home for the instance you want to operate.

As the error above stipulates, this is because the adrci home is not set. To check the homes available on the server:

ADRCI: Release 12.1.0.2.0 – Production on Tue Feb 21 22:03:57 2017

ADR base = “/u01/app/oracle”
adrci> show homes
ADR Homes:
diag/crs/myhost/crs
diag/clients/user_oracle/host_2668819005_11
diag/clients/user_oracle/host_2668819005_82
diag/rdbms/mydb/MYINSTANCE
diag/rdbms/_mgmtdb/-MGMTDB
diag/asm/+asm/+ASM1
diag/tnslsnr/myhost/listener
diag/tnslsnr/myhost/listener_scan1
diag/tnslsnr/myhost/listener_scan2
diag/tnslsnr/myhost/listener_scan3

to set the home to listener3, run the following command:

adrci> SET HOME diag/tnslsnr/myhost/listener_scan3

You should now be able to complete actions against this home, such as purging alert logs over X number of days.

2014 in review

The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog.

Here’s an excerpt:

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 88,000 times in 2014. If it were an exhibit at the Louvre Museum, it would take about 4 days for that many people to see it.

Click here to see the complete report.

Follow me on Twitter! @dbamohsin

Ive set up a new twitter handle for all things DBA – follow me @dbamohsin.

All future posts will be tweeted on this handle

Thanks

My Blog – 2011 in review

The WordPress.com stats helper monkeys prepared a 2011 annual report for this blog.

Here’s an excerpt:

The concert hall at the Syndey Opera House holds 2,700 people. This blog was viewed about 22,000 times in 2011. If it were a concert at Sydney Opera House, it would take about 8 sold-out performances for that many people to see it.

Click here to see the complete report.