Config for SQL Mail on Restart
-
My production server was shut down over the past weekend and it all came back up without problem except for emailing of alerts. No alerts were being sent out. Once I found that no alerts were being sent; I restarted the SQL Server Agent and all was fine again.
The SQL Server Agent Properties are set to Auto restart for both unexpected stoppages and the MAPI for SQL mail is configured correctly.
What else can I check?
rgds
-
Not sure how "correct" this is, but works a treat for me. SQL Mail has no "Service" so needs to be restarted within SQL Server.
Be aware that if the mail server is unavailable when SQL server starts up, you should EXEC sp_RestartSQLmail after it is available and restart SQL Server Agent service.
USE master
GO
PRINT 'sp_RestartSQLmail'
IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('dbo.sp_RestartSQLmail') AND sysstat & 0xf = 4)
DROP PROCEDURE dbo.sp_RestartSQLmail
GO
CREATE PROCEDURE dbo.sp_RestartSQLmail
AS
EXEC xp_stopmail
WAITFOR DELAY '0:0:10'
EXEC xp_startmail
GO
GRANT EXECUTE ON dbo.sp_RestartSQLmail TO public AS dbo
GO
EXEC sp_procoption @ProcName = 'sp_RestartSQLmail'
, @OptionName = 'startup'
, @OptionValue = 'on'
GO
Andy
-
Thanks Andy,
That makes sense and explains why it would have appear to have started up correctly.
Enjoy the weekend.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply