New to SQL Server Database Mail as a DBA? No worries! In this guide, let us go through the process on how to enable, disable, and check the status of Database Mail on our SQL Server instance using T-SQL scripts. Database Mail is a key feature that helps you send email notifications and alerts directly from SQL Server, keeping you updated on crucial events and jobs. With clear instructions and helpful scripts, this guide makes Database Mail management easy for new DBAs. So, let’s jump in and start managing SQL Server Database Mail!
This script checks whether the Database Mail Extended Stored Procedures are enabled or disabled on SQL Server instance.
USE msdb
GO
SELECT
[name],
[value],
value_in_use,
[description]
FROM
sys.configurations
WHERE
[name] = 'Database Mail XPs'
GO
value
column shows the configured value, while the value_in_use
column shows the value currently in effect. If the value_in_use
is 1, the Database Mail feature is enabled; if it’s 0, the feature is disabled.
Remember that if you made a change to the ‘Database Mail XPs’ configuration, you might need to restart the SQL Server service for the change to take effect. This script enables the Database Mail feature on your SQL Server instance. By default, Database Mail is disabled, and you need to enable it to use the feature.
--Enable Database Mail feature:
USE msdb
GO
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE;
GO
This script disables the Database Mail Extended Stored Procedures on your SQL Server instance
USE msdb;
GO
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'Database Mail XPs', 0;
RECONFIGURE;
GO