SQL Server Restart Email Alert شرح عربي
USE master
GO
----Enable scan for Startup Procs
EXEC sys.sp_configure N'Show Advanced Options', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'scan for startup procs', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO
----Creating Alert Proc ** Must be in Master
CREATE OR ALTER PROC spSQLServerRebootAlert
AS
BEGIN TRY
SET NOCOUNT ON
DECLARE @Services TABLE (ID SMALLINT IDENTITY ,Status VARCHAR(100))
DECLARE @body VARCHAR (255),@subject VARCHAR(100)
SELECT @body= @@SERVERNAME+' Was Rebooted at '+CONVERT(varchar(25),GETDATE(),100)+CHAR(10),
@subject= @@SERVERNAME+' Was Rebooted'
INSERT INTO @Services(Status)
EXEC xp_servicecontrol N'querystate',N'MSSQLServer'
UPDATE @Services SET Status = 'SQL Server Service: '+(Select TOP 1 Status FROM @Services WHERE ID=1) WHERE ID=1
WAITFOR DELAY '00:00:03'
INSERT INTO @Services
EXEC xp_servicecontrol N'querystate',N'SQLServerAGENT'
UPDATE @Services SET Status = 'SQL Server AGENT Service: '+(Select TOP 1 Status FROM @Services WHERE ID=2) WHERE ID=2
SELECT @body = @body+CHAR(10)+Status FROM @Services
-----Send Email Via DatabaseMail
EXEC msdb.dbo.sp_send_dbmail
@profile_name = '',
@recipients = '',
@body = @body,
@subject = @subject;
SET NOCOUNT OFF
END TRY BEGIN CATCH END CATCH
GO
----Config Proc to run at Startup
EXEC sp_procoption 'spSQLServerRebootAlert', 'startup', 'true'
GO
----To Verify
SELECT name FROM sys.objects
WHERE OBJECTPROPERTY(OBJECT_ID, 'ExecIsStartup') = 1
select * from sys.procedures where is_auto_executed = 1
---- to Turn off this Feature
--EXEC sys.sp_configure N'Show Advanced Options', N'1'
--GO
--RECONFIGURE WITH OVERRIDE
--GO
--EXEC sys.sp_configure N'scan for startup procs', N'0'
--GO
--RECONFIGURE WITH OVERRIDE
--GO
--EXEC sp_procoption 'spSQLServerRebootAlert', 'startup', 'false'