Database Maintenance Plan Bug

We have an environment where we utilize master-target topology. When we
create a maintenance plan, we are able to add as many servers as we want.
After the plan is created, we are unable to add additional servers on the
master server, and when we open a plan, all the servers are unchecked. Does
anyone know if this is a bug, and if there is a patch to it? Does anyone know
how to add additional servers after the plan is created?
There are several issues with the MP in that it is not quite capable of
doing everything you would expect. This is a one I haven't heard before but
it is somewhat consistent with other bugs that I have seen. The MP wizard
gets it's information from several sources when you open the wizard. One is
the MP system tables and another is that it parses the command strings in
the jobs it creates. It is very likely it has troubles parsing master -
target type jobs. It should be pretty easy to create your own to do the
same thing though.
Andrew J. Kelly SQL MVP
"MikeS" <MikeS@.discussions.microsoft.com> wrote in message
news:DA16B809-ED8C-412C-83BE-A328637CFA25@.microsoft.com...
> We have an environment where we utilize master-target topology. When we
> create a maintenance plan, we are able to add as many servers as we want.
> After the plan is created, we are unable to add additional servers on the
> master server, and when we open a plan, all the servers are unchecked.
> Does
> anyone know if this is a bug, and if there is a patch to it? Does anyone
> know
> how to add additional servers after the plan is created?
|||We have a very large sql shop, and I would like to have an automated way to
know on which servers I have the maintenance plan rolled out. Could you
please provide an example, of what you mean "easy to create your own " I can
create a string which will build a plan, but it means I will have to "rerun
it" every time I need to add a new server. Is there a sufficient way of
creating this?
thank you
"Andrew J. Kelly" wrote:

> There are several issues with the MP in that it is not quite capable of
> doing everything you would expect. This is a one I haven't heard before but
> it is somewhat consistent with other bugs that I have seen. The MP wizard
> gets it's information from several sources when you open the wizard. One is
> the MP system tables and another is that it parses the command strings in
> the jobs it creates. It is very likely it has troubles parsing master -
> target type jobs. It should be pretty easy to create your own to do the
> same thing though.
> --
> Andrew J. Kelly SQL MVP
>
> "MikeS" <MikeS@.discussions.microsoft.com> wrote in message
> news:DA16B809-ED8C-412C-83BE-A328637CFA25@.microsoft.com...
>
>
|||Mike,
What I meant was that it is fairly easy to create custom jobs to do things
such as Backup, Reindex etc. Once you have the scripts it is again pretty
easy to deploy them to any server. You can use DMO or even oSql to issue
the commands to any server you connect to. You can also use DMO or oSql to
check for the existence of any job by simply running the proper query or
sp_helpJob etc. If you have that many servers where it becomes hard to
manage I have two suggestions. One is that you might want to consider
consolidating several servers into a single larger one. This can save costs
on licenses and hardware over many smaller servers. They are also easier to
maintain. But I highly recommend you look at a 3rd party product from
http://www.sqlsentry.net/ that can help you to manage your jobs at a much
more efficient way.
Andrew J. Kelly SQL MVP
"MikeS" <MikeS@.discussions.microsoft.com> wrote in message
news:B46E86CA-F89E-4E79-A796-32BF781A601C@.microsoft.com...[vbcol=seagreen]
> We have a very large sql shop, and I would like to have an automated way
> to
> know on which servers I have the maintenance plan rolled out. Could you
> please provide an example, of what you mean "easy to create your own " I
> can
> create a string which will build a plan, but it means I will have to
> "rerun
> it" every time I need to add a new server. Is there a sufficient way of
> creating this?
> thank you
> "Andrew J. Kelly" wrote:

Database Maintenance Plan Bug

We have an environment where we utilize master-target topology. When we
create a maintenance plan, we are able to add as many servers as we want.
After the plan is created, we are unable to add additional servers on the
master server, and when we open a plan, all the servers are unchecked. Does
anyone know if this is a bug, and if there is a patch to it? Does anyone kno
w
how to add additional servers after the plan is created?There are several issues with the MP in that it is not quite capable of
doing everything you would expect. This is a one I haven't heard before but
it is somewhat consistent with other bugs that I have seen. The MP wizard
gets it's information from several sources when you open the wizard. One is
the MP system tables and another is that it parses the command strings in
the jobs it creates. It is very likely it has troubles parsing master -
target type jobs. It should be pretty easy to create your own to do the
same thing though.
Andrew J. Kelly SQL MVP
"MikeS" <MikeS@.discussions.microsoft.com> wrote in message
news:DA16B809-ED8C-412C-83BE-A328637CFA25@.microsoft.com...
> We have an environment where we utilize master-target topology. When we
> create a maintenance plan, we are able to add as many servers as we want.
> After the plan is created, we are unable to add additional servers on the
> master server, and when we open a plan, all the servers are unchecked.
> Does
> anyone know if this is a bug, and if there is a patch to it? Does anyone
> know
> how to add additional servers after the plan is created?|||We have a very large sql shop, and I would like to have an automated way to
know on which servers I have the maintenance plan rolled out. Could you
please provide an example, of what you mean "easy to create your own " I can
create a string which will build a plan, but it means I will have to "rerun
it" every time I need to add a new server. Is there a sufficient way of
creating this?
thank you
"Andrew J. Kelly" wrote:

> There are several issues with the MP in that it is not quite capable of
> doing everything you would expect. This is a one I haven't heard before b
ut
> it is somewhat consistent with other bugs that I have seen. The MP wizard
> gets it's information from several sources when you open the wizard. One
is
> the MP system tables and another is that it parses the command strings in
> the jobs it creates. It is very likely it has troubles parsing master -
> target type jobs. It should be pretty easy to create your own to do the
> same thing though.
> --
> Andrew J. Kelly SQL MVP
>
> "MikeS" <MikeS@.discussions.microsoft.com> wrote in message
> news:DA16B809-ED8C-412C-83BE-A328637CFA25@.microsoft.com...
>
>|||Mike,
What I meant was that it is fairly easy to create custom jobs to do things
such as Backup, Reindex etc. Once you have the scripts it is again pretty
easy to deploy them to any server. You can use DMO or even oSql to issue
the commands to any server you connect to. You can also use DMO or oSql to
check for the existence of any job by simply running the proper query or
sp_helpJob etc. If you have that many servers where it becomes hard to
manage I have two suggestions. One is that you might want to consider
consolidating several servers into a single larger one. This can save costs
on licenses and hardware over many smaller servers. They are also easier to
maintain. But I highly recommend you look at a 3rd party product from
http://www.sqlsentry.net/ that can help you to manage your jobs at a much
more efficient way.
Andrew J. Kelly SQL MVP
"MikeS" <MikeS@.discussions.microsoft.com> wrote in message
news:B46E86CA-F89E-4E79-A796-32BF781A601C@.microsoft.com...[vbcol=seagreen]
> We have a very large sql shop, and I would like to have an automated way
> to
> know on which servers I have the maintenance plan rolled out. Could you
> please provide an example, of what you mean "easy to create your own " I
> can
> create a string which will build a plan, but it means I will have to
> "rerun
> it" every time I need to add a new server. Is there a sufficient way of
> creating this?
> thank you
> "Andrew J. Kelly" wrote:
>

Database Maintenance Plan Bug

We have an environment where we utilize master-target topology. When we
create a maintenance plan, we are able to add as many servers as we want.
After the plan is created, we are unable to add additional servers on the
master server, and when we open a plan, all the servers are unchecked. Does
anyone know if this is a bug, and if there is a patch to it? Does anyone know
how to add additional servers after the plan is created?There are several issues with the MP in that it is not quite capable of
doing everything you would expect. This is a one I haven't heard before but
it is somewhat consistent with other bugs that I have seen. The MP wizard
gets it's information from several sources when you open the wizard. One is
the MP system tables and another is that it parses the command strings in
the jobs it creates. It is very likely it has troubles parsing master -
target type jobs. It should be pretty easy to create your own to do the
same thing though.
--
Andrew J. Kelly SQL MVP
"MikeS" <MikeS@.discussions.microsoft.com> wrote in message
news:DA16B809-ED8C-412C-83BE-A328637CFA25@.microsoft.com...
> We have an environment where we utilize master-target topology. When we
> create a maintenance plan, we are able to add as many servers as we want.
> After the plan is created, we are unable to add additional servers on the
> master server, and when we open a plan, all the servers are unchecked.
> Does
> anyone know if this is a bug, and if there is a patch to it? Does anyone
> know
> how to add additional servers after the plan is created?|||We have a very large sql shop, and I would like to have an automated way to
know on which servers I have the maintenance plan rolled out. Could you
please provide an example, of what you mean "easy to create your own " I can
create a string which will build a plan, but it means I will have to "rerun
it" every time I need to add a new server. Is there a sufficient way of
creating this?
thank you
"Andrew J. Kelly" wrote:
> There are several issues with the MP in that it is not quite capable of
> doing everything you would expect. This is a one I haven't heard before but
> it is somewhat consistent with other bugs that I have seen. The MP wizard
> gets it's information from several sources when you open the wizard. One is
> the MP system tables and another is that it parses the command strings in
> the jobs it creates. It is very likely it has troubles parsing master -
> target type jobs. It should be pretty easy to create your own to do the
> same thing though.
> --
> Andrew J. Kelly SQL MVP
>
> "MikeS" <MikeS@.discussions.microsoft.com> wrote in message
> news:DA16B809-ED8C-412C-83BE-A328637CFA25@.microsoft.com...
> > We have an environment where we utilize master-target topology. When we
> > create a maintenance plan, we are able to add as many servers as we want.
> > After the plan is created, we are unable to add additional servers on the
> > master server, and when we open a plan, all the servers are unchecked.
> > Does
> > anyone know if this is a bug, and if there is a patch to it? Does anyone
> > know
> > how to add additional servers after the plan is created?
>
>|||Mike,
What I meant was that it is fairly easy to create custom jobs to do things
such as Backup, Reindex etc. Once you have the scripts it is again pretty
easy to deploy them to any server. You can use DMO or even oSql to issue
the commands to any server you connect to. You can also use DMO or oSql to
check for the existence of any job by simply running the proper query or
sp_helpJob etc. If you have that many servers where it becomes hard to
manage I have two suggestions. One is that you might want to consider
consolidating several servers into a single larger one. This can save costs
on licenses and hardware over many smaller servers. They are also easier to
maintain. But I highly recommend you look at a 3rd party product from
http://www.sqlsentry.net/ that can help you to manage your jobs at a much
more efficient way.
--
Andrew J. Kelly SQL MVP
"MikeS" <MikeS@.discussions.microsoft.com> wrote in message
news:B46E86CA-F89E-4E79-A796-32BF781A601C@.microsoft.com...
> We have a very large sql shop, and I would like to have an automated way
> to
> know on which servers I have the maintenance plan rolled out. Could you
> please provide an example, of what you mean "easy to create your own " I
> can
> create a string which will build a plan, but it means I will have to
> "rerun
> it" every time I need to add a new server. Is there a sufficient way of
> creating this?
> thank you
> "Andrew J. Kelly" wrote:
>> There are several issues with the MP in that it is not quite capable of
>> doing everything you would expect. This is a one I haven't heard before
>> but
>> it is somewhat consistent with other bugs that I have seen. The MP
>> wizard
>> gets it's information from several sources when you open the wizard. One
>> is
>> the MP system tables and another is that it parses the command strings in
>> the jobs it creates. It is very likely it has troubles parsing master -
>> target type jobs. It should be pretty easy to create your own to do the
>> same thing though.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "MikeS" <MikeS@.discussions.microsoft.com> wrote in message
>> news:DA16B809-ED8C-412C-83BE-A328637CFA25@.microsoft.com...
>> > We have an environment where we utilize master-target topology. When we
>> > create a maintenance plan, we are able to add as many servers as we
>> > want.
>> > After the plan is created, we are unable to add additional servers on
>> > the
>> > master server, and when we open a plan, all the servers are unchecked.
>> > Does
>> > anyone know if this is a bug, and if there is a patch to it? Does
>> > anyone
>> > know
>> > how to add additional servers after the plan is created?
>>

Database Mail Problems / Job Notifications...

I've read an extensive bit about this problem in several forums and so far none of the information has helped me to determine if there is a bug in SQL 2005 x64 regardless of version or SP or if I just don't know how to do this correctly.

I can send a test email from db mail but when a job fails it does not send an email to an operator. I have monitored the IIS logs to see if it is trying to send and it is not. Database mail is enabled in Surface AC and in SQL Agent [one odd thing is that the 'test' button is only available on the SQL Agent Alerts page if I use SQL Mail]. No specific error happens (in the mail log, sql agent log, or application event log) when I try to send mail from a failed job. Mail accounts and credentials are valid. Please note that in the steps listed below, I have restarted the SQL Agent Service each time I make a change.

I've tried deleting and recreating the operators (they are enabled). I've tried using a mail server that uses anonymous authentication as well (I changed this back to one that uses basic authentication). The profiles were made default and public. (Though this shouldn't ever have to be done, as the account is a sysadmin) the SQLServer2005SQLAgentUser was explicitly added to the msdb DatabaseMail user role.

This query works, so I know that the job is not trying to execute the sp:

USE msdb ;

GO

EXEC dbo.sp_notify_operator

@.profile_name = N'staging monitor',

@.name = N'Charles Evans',

@.subject = N'Test Notification',

@.body = N'Successfully emailed profile.' ;

GO

This is happening in SQL 2005 x64 EE and x64 DE SP1 and SP2 respectively. Any thoughts on what else this could be? The same setup works fine in SQL 2005 SE x86 SP1.

Check the following article...

How to configure SQL Server Agent to send job status notifications and alert notifications in SQL Server 2005 64-bit editions

http://support.microsoft.com/kb/908360

|||So the only way is to use SQL Mail, instead of database mail?|||

SQL Server Agent is not integrated with Database Mail or with SQL Mail in Microsoft SQL Server 2005 64-bit editions.

Please read the article... you can setup mail using SMTP where SQL mail uses MAPI ...

|||

In SP0 this is true - according to the article:

"In SQL Server 2005 Service Pack 1 (SP1), you can use SQL Server Agent to interact with Database Mail as the mailing system on 64-bit operating systems."

I've made this work in our production system now with SP-1 EE x64, there was a permissions issue. However, things are not working in our staging environment SP-2 DE x64 with the same setup and account settings/permissions.

I'll keep posted if I figure out why not in staging.

Database mail does not work from Sql Server Agent Job after upgrade to SP1

Upgraded SQL Server 2005 x64 to Service Pack 1 expressly for the purpose of enabling Database Mail from SQL Job, which was a known bug that was listed as fixed in this Service Pack. It still does not work; in fact after installing SP1 on an x86 SQL Server Standard, Database Mail does not work from there now either, although it did prior to the upgrade! I am using Integrated Security.

Error message received: The job succeeded. The Job was invoked by User <name>. The last step to run was step 1 (Select). NOTE: Failed to notify 'Mike Schelstrate' via email.

Message in Error Log: [264] An attempt was made to send an email when no email session has been established.

Send test Email does work on both Servers.

Here is another Error message I found in the SQL Error Logs: [298] SQLServer Error: 2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs). Looks like this may be the root of the problem because it does not exist, I checked. How do I obtain this missing extended stored procedure?

I had the exact same problem with the same error on 2005. To fix it, I did the following:

1. Went into object-explorer, right-clicked on the SQL Server Agent, and selected properties. Then went to the Alert System page, and enabled the mail profile for the server agent. (you may already have this).

2. Then, went to "Database mail", right-clicked and selected "Configure Database mail." Selected "Manage profile security" and made sure my profile was set to public. THEN, and this is the kicker, clicked on the "Default Profile" field and set it to "yes".

|||

I am still having issues after working through all these steps. Any other suggestions.

In general I seem to be having a lot of issues with the 64 Bit on 2005.

|||

I'm having the exact same issue.

I have SQL Server Standard Edition 2005 (SP1) running and the Database Mail is not working correctly. I get a message in the SQL Agent log saying:

"SQLServer Error: 2812, Could not find stored procedure msdb.dbo.xp_sqlagent_notify"

Any solutions to this? What is this stored procedure? I can't find any documentation on it on Microsoft's site or anywhere for that matter.

|||

I just started working on the mail on my test server and I have the same problem.

I have followed the instruction above to no avail. I have set up a job, when it completes it is to email me. I have set up mail and have been able to send a test mail by right clicking the db mail and selecting send test mail. I have se my self up as an operator. I have enabled db mail. The only thing I find odd is when I go to SQL server agent and select properties then select alert system the test button is grayed out.

|||

Ok I found another test I could run to see if I have it configured correctly.

EXEC msdb.dbo.sp_send_dbmail

@.recipients=N'User@.Place.com',

@.subject=N'just another mail test.',

@.body=N'just another mail test.',

@.profile_name = 'EMail';

When I run this I get the response that the mail is queued. Shortly there after I get the mail. In my case there must be an issue between the jobs and the operator.

|||

Ok it looks like I might have solve it for my self. Follow this link

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=322173&SiteID=1

Database mail does not work from Sql Server Agent Job after upgrade to SP1

Upgraded SQL Server 2005 x64 to Service Pack 1 expressly for the purpose of enabling Database Mail from SQL Job, which was a known bug that was listed as fixed in this Service Pack. It still does not work; in fact after installing SP1 on an x86 SQL Server Standard, Database Mail does not work from there now either, although it did prior to the upgrade! I am using Integrated Security.

Error message received: The job succeeded. The Job was invoked by User <name>. The last step to run was step 1 (Select). NOTE: Failed to notify 'Mike Schelstrate' via email.

Message in Error Log: [264] An attempt was made to send an email when no email session has been established.

Send test Email does work on both Servers.

Here is another Error message I found in the SQL Error Logs: [298] SQLServer Error: 2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs). Looks like this may be the root of the problem because it does not exist, I checked. How do I obtain this missing extended stored procedure?

I had the exact same problem with the same error on 2005. To fix it, I did the following:

1. Went into object-explorer, right-clicked on the SQL Server Agent, and selected properties. Then went to the Alert System page, and enabled the mail profile for the server agent. (you may already have this).

2. Then, went to "Database mail", right-clicked and selected "Configure Database mail." Selected "Manage profile security" and made sure my profile was set to public. THEN, and this is the kicker, clicked on the "Default Profile" field and set it to "yes".

|||

I am still having issues after working through all these steps. Any other suggestions.

In general I seem to be having a lot of issues with the 64 Bit on 2005.

|||

I'm having the exact same issue.

I have SQL Server Standard Edition 2005 (SP1) running and the Database Mail is not working correctly. I get a message in the SQL Agent log saying:

"SQLServer Error: 2812, Could not find stored procedure msdb.dbo.xp_sqlagent_notify"

Any solutions to this? What is this stored procedure? I can't find any documentation on it on Microsoft's site or anywhere for that matter.

|||

I just started working on the mail on my test server and I have the same problem.

I have followed the instruction above to no avail. I have set up a job, when it completes it is to email me. I have set up mail and have been able to send a test mail by right clicking the db mail and selecting send test mail. I have se my self up as an operator. I have enabled db mail. The only thing I find odd is when I go to SQL server agent and select properties then select alert system the test button is grayed out.

|||

Ok I found another test I could run to see if I have it configured correctly.

EXEC msdb.dbo.sp_send_dbmail

@.recipients=N'User@.Place.com',

@.subject=N'just another mail test.',

@.body=N'just another mail test.',

@.profile_name = 'EMail';

When I run this I get the response that the mail is queued. Shortly there after I get the mail. In my case there must be an issue between the jobs and the operator.

|||

Ok it looks like I might have solve it for my self. Follow this link

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=322173&SiteID=1