{"id":7,"date":"2017-08-01T20:52:23","date_gmt":"2017-08-01T20:52:23","guid":{"rendered":"http:\/\/www.spk.com\/?p=7"},"modified":"2017-08-01T20:54:09","modified_gmt":"2017-08-01T20:54:09","slug":"sql-server-configuring-troubleshooting-database-mail","status":"publish","type":"post","link":"https:\/\/www.spktechfit.com\/?p=7","title":{"rendered":"SQL Server: Configuring & Troubleshooting Database Mail"},"content":{"rendered":"

Database mail is great tool which allows you to send notification emails from your SQL Server. You can use to send notifications for things like backups, error, resource alerts etc…<\/p>\n

Configuring Database Mail<\/h2>\n

Using the SSMS Wizard<\/b><\/p>\n

    \n
  1. Go to Management -> Right Click on ‘Database Mail’ and select ‘Configure Database Mail’<\/li>\n
  2. Select the first option ‘Setup Database Mail by performing the following tasks<\/li>\n
  3. Give the Profile a name. The profile is a container for the email accounts. You can have multiple email accounts for a profile.<\/li>\n
  4. Click on the Add button in the SMTP Accounts section<\/li>\n
  5. Enter the details for your email account. I used my\u00a0gmail\u00a0account. You might need to check with your email provider for the settings for your account
    \n\"\"<\/li>\n
  6. Click Ok. You can add multiple accounts if you want. It will try the email accounts by the\u00a0priority\u00a0that they are set to.<\/li>\n
  7. On the Security page you can set it as a public or private profile. Public means the profile can be used by all users of the database. I set it as Public.<\/li>\n
  8. On the System Parameter page you can configure some more advanced settings if you want. Things like retry attempts, max file size etc… I left it as the default<\/li>\n
  9. Click Next, then Finish and it should configure without any errors<\/li>\n<\/ol>\n

    Test your configuration by right clicking on Database Mail and selecting ‘Send a Test email’. Enter your email address to test. If it doesn\u2019t work, see the Troubleshooting section<\/p>\n

     <\/p>\n

    Using T-SQL:<\/b><\/p>\n

    –Add mail account<\/b><\/p>\n

    EXEC\u00a0msdb.dbo.sysmail_add_account_sp<\/p>\n

    @account_name\u00a0= ‘Gmail’,<\/p>\n

    @email_address\u00a0= ‘keenaghan.sean@gmail.com<\/a>‘,<\/p>\n

    @mailserver_name\u00a0= ‘smtp.gmail.com’,<\/p>\n

    @port = 587,<\/p>\n

    @enable_ssl\u00a0= 1,<\/p>\n

    @username = ‘keenaghan.sean@gmail.com<\/a>‘,<\/p>\n

    @password = ‘******’<\/p>\n

    –Add mail profile, storing new ID in output\u00a0parameters<\/b><\/p>\n

    DECLARE @profID\u00a0int<\/p>\n

    EXEC\u00a0msdb.dbo.sysmail_add_profile_sp<\/p>\n

    @profile_name\u00a0= ‘SQL Emails’,<\/p>\n

    @description = ‘Profile used for administrative mail.’,<\/p>\n

    @profile_id\u00a0= @profID\u00a0OUTPUT<\/p>\n

    –Add mail account to profile, using @profID\u00a0from above<\/b><\/p>\n

    EXEC\u00a0msdb.dbo.sysmail_add_profileaccount_sp<\/p>\n

    @profile_id\u00a0= @profID,<\/p>\n

    @account_name\u00a0= ‘Gmail’,<\/p>\n

    @sequence_number\u00a0= 1<\/p>\n

    –Test the configuration by sending an email<\/b><\/p>\n

    EXEC\u00a0msdb.dbo.sp_send_dbmail<\/p>\n

    @profile_name=’SQL Emails’,<\/p>\n

    @recipients=’keenaghan.sean@gmail.com<\/a>‘,<\/p>\n

    @subject=’Testing Emails’,<\/p>\n

    @body=’If you\u00a0recieved\u00a0this email, it worked!’<\/p>\n

    <\/h2>\n

    Troubleshooting Database Mail<\/b><\/h1>\n
    To check the Database Mail logs using SSMS:<\/h5>\n
      \n
    1. Go to SQL\u00a0Server Agent -> Error Logs<\/li>\n
    2. Selected the required data range<\/li>\n
    3. Tick the ‘Database\u00a0Mail’ checkbox in the ‘Select Logs’ section on the left<\/li>\n<\/ol>\n

       <\/p>\n

      To check the Database Mail logs using T-SQL:<\/b>
      \nThere is a system table which stores the Database mail logs. To view this table using T-SQL:<\/p>\n

      SELECT * FROM\u00a0msdb.dbo.sysmail_log
      \n<\/i>GO<\/i><\/p>\n","protected":false},"excerpt":{"rendered":"

      Database mail is great tool which allows you to send notification emails from your SQL Server. You can use to send notifications for things like backups, error, resource alerts etc… Configuring Database Mail Using the SSMS Wizard Go to Management -> Right Click on ‘Database Mail’ and select ‘Configure Database Mail’ Select the first option […]<\/p>\n","protected":false},"author":1,"featured_media":9,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"footnotes":""},"categories":[2,3],"tags":[],"class_list":["post-7","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql-server","category-it"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t