March 1, 2012 at 11:32 am
I have a SQL2008R2 SP1 CU4 cluster on Win2008R2. I have setup DBMail. I do a test email from Mgt Studio and it sends the email just fine. I have valid Operators setup with correct email addresses and doing a manual execute of the below statement it sends emails just fine.
EXEC msdb.dbo.sp_send_dbmail @profile_name='DBA Mail', @recipients='some_name@company.com',@subject='Full DB Backup Failed', @body='The Backup Failed.'
go
However, the problem is that when I setup the job Notifications for success to send an email it never sends it. I look in the DB mail log and it doesn't say it fails sending it.
I have setup DB Mail on many SQL Servers and this is the first one that has stumped me. Any thoughts?
March 1, 2012 at 12:04 pm
I'm gonna ask a stupid question, sorry, but did you configure SQL Agent to USE DBMail?
CEWII
March 1, 2012 at 12:17 pm
No question is stupid... AND you my friend found the issue. Dang! I missed doing that step.
Thanks a million!
March 1, 2012 at 12:22 pm
You are welcome.
I just hate asking the REALLY simple questions, I don't want people to think I am talking down to them.
And this is a REALLY easy step to miss. I've hit it a few times and it rates a face-palm when I do it..
CEWII
March 1, 2012 at 12:25 pm
Well, I knew it was probably something simple. I have messed around with it for about 30 minutes, then deleted the mail profile and messed around with that and just could not figure what I was missing. It is in my DB Mail config document to do that too.
Thanks for pointing out the obvious! Sometimes it is the little things... a second set of eyes.
March 1, 2012 at 12:45 pm
I have a script to setup DBMail on my servers, but I still have to do Agent myself, maybe I'll script that too..
USE [msdb]
GO
EXEC dbo.sysmail_add_account_sp
@account_name = 'DBA',
@email_address = 'dba@yourdomain.com',
@display_name = 'SQL(SQLSERVERNAME\instance)',
@replyto_address = 'dba@yourdomain.com',
@description = '',
@mailserver_name = 'smtp.yourdomain.com',
@mailserver_type = 'SMTP',
@port = 25,
@use_default_credentials = 0,
@enable_ssl = 0
GO
EXEC sysmail_add_profile_sp
@profile_name = 'DBA',
@description = ''
GO
EXEC msdb.dbo.sysmail_add_profileaccount_sp @profile_name=N'DBA', @account_name=N'DBA', @sequence_number=1
GO
EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'AccountRetryAttempts', @parameter_value=N'3', @description=N'Number of retry attempts for a mail server'
GO
EXEC msdb.dbo.sysmail_configure_sp @parameter_name=N'MaxFileSize', @parameter_value=N'10000000', @description=N'Default maximum file size'
GO
I have this as well as my PBM policies and auditing that I apply to all servers. I hate typing more than I have to..
CEWII
March 1, 2012 at 1:04 pm
If you add:
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1
GO
EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'DBA'
GO
It will configure SQL Agent as well.
CEWII
March 1, 2012 at 1:19 pm
I never thought about scripting it out and just running the scripts. I have the email operators scripted out though.
March 1, 2012 at 1:36 pm
I just hate having to manually set all that up. I use the same base mail config for all servers. That script gets minor tweaks for the from name so I can tell what server it came from but other than that its the same everywhere..
Because we are using a lot of Enterprise Edition I also have a number of standard Audits.
And its a bit obscure but Policy Based Management is a great way to keep your configuration in a known state. Such things as database owners being a single user, or SQL password complexity set, or database growth minimums.. I have 11 policies currently that helps me know immediately when I connect to the server that it is no longer in compliance and I need to look around a bit..
Also since maintenance plan jobs carry the owner of the person who saved the plan last I have a job that runs hourly and sets the owner of maintenance to sa. In previous jobs we had someone leave and their login owned maintenance plan jobs that started failing when we disabled their login.. Hate it when that happens.
All this really cuts down on the number of things I have to manually check.
CEWII
March 1, 2012 at 1:41 pm
Yea, I hear you. The powers at be keep talking about the # of servers and to consolidate and then we keep getting new apps that require another server for this reason or that reason. We consolidate some but add new stuff so we never reduce really.. LOL.
I have scripted out some stuff for new installs to run scripts but not nearly to the degree you have.
The Maint plan owner thing is a royal pain I agree.
March 1, 2012 at 1:53 pm
The number of servers is an ongoing battle. Part of that battle is changing assumptions, many start from the "we need another server" assumption but where we should start from is the "what do you need" position and then we'll decide if a new server is needed or perhaps a new instance on existing hardware or its smal enough to co-exist with other databases.. I just don't let the business make those kinds of decisions, more often than not they do not have enough information to accurately decide, and there are MANY applications that co-exist..
CEWII
March 1, 2012 at 1:59 pm
Yep. We do the same thing. However, some need to be behind the DMZ, on a different domain for our stores, be at a different version or SP ...etc....
March 1, 2012 at 2:43 pm
Elliott Whitlow (3/1/2012)
...
Also since maintenance plan jobs carry the owner of the person who saved the plan last I have a job that runs hourly and sets the owner of maintenance to sa. In previous jobs we had someone leave and their login owned maintenance plan jobs that started failing when we disabled their login.. Hate it when that happens.
...
You only have to change the owner of the maintenance plan once, and after that the jobs will stay with that owner. This script will update the owner of all maintenance plans on a SQL Server 2005, 2008, or 2008 R2 server to the owner entered in variable @new_owner.
Change Maintenance Plan Owner
March 1, 2012 at 2:46 pm
Thanks for the script.
CEWII
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply