Sql-server – Error when modifying Notify Operator task in maintenance plan

maintenance-planssql serversql-server-2016

I'm unable to modify a Notify Operator task in my new maintenance plan.
SQL Server Management Studio displays this error after double clicking on the task:

Property IsMailHost is not available for database '[msdb]'. This property may not exist for this object, or may not be retrievable due to insufficient access right.

The other tasks in the maintenance plan are working successfully.
I'm using SQL Server 2016 Standard (13.0.1601.5) and SQL Sever Management Studio 16.5.
The database mail is also configured with a public profile.

Best Answer

From the message you posted I can see MSDB is not set for Mail Host Status.

Database.IsMailHost Property -- Gets a value that specifies whether the database is configured as a mail host.

To find whether the Database is a Mail Host or not, use the below PowerShell command. I tested it on SQL Server 2014.

Use the below code in Powershell ISE to check: (For Default Instance Only)

$srv = new-Object Microsoft.SqlServer.Management.Smo.Server("(local)")
$db = New-Object Microsoft.SqlServer.Management.Smo.Database
$db = $srv.Databases.Item("MSDB") 
Write-Host "Database is a mail host :" $db.IsMailHost

The MSDB database is the Database Mail host database. This database contains the stored procedures and messaging objects for Database Mail.

By default, Database Mail is not active.

You must explicitly enable Database Mail by using either

  1. The Database Mail Configuration Wizard

or

  1. The Database Mail XPs Option of the sp_configure system stored procedure.

The following example enables the Database Mail extended stored procedures.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
Related Question