I need to change a column that is a six character varchar
called meeting_date to a datetime value. My plan is to
create a table that stores a int value
and a datetime value. Copy the primary key (an identity
column) and converted the value to a datetime on the
insert. Disable or alter all objects, especially triggers,
that use the column meeting_date. Then drop the
meeting_date column and add the column as a datetime
value. Wait for replication to make the change. Update
the new datetime column meeting_date with the converted
datetime value joining on the identity column.
Reestablish all objects and update all applications that
use that the meeting_date column.
Will this work with replication ? Is there a better
way ? etc
We are using merge replication and the column has 200,000
values of a 3,900,000 row table.
It is fairly easy to convert meeting_date value itself to
datetime. I am a developer not a DBA so I do not know all
the nuances of replication.
You realize that replication prevents schema changes to tables that are
being replicated. You must drop the publication before you make your
change. Once your change is made, you must re-create the publication and
generate a new snapshot. You stated that this table contains 3.9 million
rows. Are there any other large tables in this publication? What is the
line speed between the publisher and the subscriber(s)? The point that I'm
trying to make here is that the resynchronization could take a very long
time.
How big is the database? Would it be practical to burn it to a CD or DVD
and physically transport it to the subscriber(s)? That might turn out to be
faster than trying to push out a new snapshot.
Also, you might want to investigate using sp_repladdcolumn and
sp_repldropcolumn.
Mike
"Phil396" <anonymous@.discussions.microsoft.com> wrote in message
news:502e01c52328$6d842d10$a601280a@.phx.gbl...
> I need to change a column that is a six character varchar
> called meeting_date to a datetime value. My plan is to
> create a table that stores a int value
> and a datetime value. Copy the primary key (an identity
> column) and converted the value to a datetime on the
> insert. Disable or alter all objects, especially triggers,
> that use the column meeting_date. Then drop the
> meeting_date column and add the column as a datetime
> value. Wait for replication to make the change. Update
> the new datetime column meeting_date with the converted
> datetime value joining on the identity column.
> Reestablish all objects and update all applications that
> use that the meeting_date column.
>
> Will this work with replication ? Is there a better
> way ? etc
> We are using merge replication and the column has 200,000
> values of a 3,900,000 row table.
> It is fairly easy to convert meeting_date value itself to
> datetime. I am a developer not a DBA so I do not know all
> the nuances of replication.
Showing posts with label plan. Show all posts
Showing posts with label plan. Show all posts
Tuesday, March 20, 2012
Changing a column type with replication
I need to change a column that is a six character varchar
called meeting_date to a datetime value. My plan is to
create a table that stores a int value
and a datetime value. Copy the primary key (an identity
column) and converted the value to a datetime on the
insert. Disable or alter all objects, especially triggers,
that use the column meeting_date. Then drop the
meeting_date column and add the column as a datetime
value. Wait for replication to make the change. Update
the new datetime column meeting_date with the converted
datetime value joining on the identity column.
Reestablish all objects and update all applications that
use that the meeting_date column.
Will this work with replication ? Is there a better
way ? etc
We are using merge replication and the column has 200,000
values of a 3,900,000 row table.
It is fairly easy to convert meeting_date value itself to
datetime. I am a developer not a DBA so I do not know all
the nuances of replication.You realize that replication prevents schema changes to tables that are
being replicated. You must drop the publication before you make your
change. Once your change is made, you must re-create the publication and
generate a new snapshot. You stated that this table contains 3.9 million
rows. Are there any other large tables in this publication? What is the
line speed between the publisher and the subscriber(s)? The point that I'm
trying to make here is that the resynchronization could take a very long
time.
How big is the database? Would it be practical to burn it to a CD or DVD
and physically transport it to the subscriber(s)? That might turn out to be
faster than trying to push out a new snapshot.
Also, you might want to investigate using sp_repladdcolumn and
sp_repldropcolumn.
Mike
"Phil396" <anonymous@.discussions.microsoft.com> wrote in message
news:502e01c52328$6d842d10$a601280a@.phx.gbl...
> I need to change a column that is a six character varchar
> called meeting_date to a datetime value. My plan is to
> create a table that stores a int value
> and a datetime value. Copy the primary key (an identity
> column) and converted the value to a datetime on the
> insert. Disable or alter all objects, especially triggers,
> that use the column meeting_date. Then drop the
> meeting_date column and add the column as a datetime
> value. Wait for replication to make the change. Update
> the new datetime column meeting_date with the converted
> datetime value joining on the identity column.
> Reestablish all objects and update all applications that
> use that the meeting_date column.
>
> Will this work with replication ? Is there a better
> way ? etc
> We are using merge replication and the column has 200,000
> values of a 3,900,000 row table.
> It is fairly easy to convert meeting_date value itself to
> datetime. I am a developer not a DBA so I do not know all
> the nuances of replication.
called meeting_date to a datetime value. My plan is to
create a table that stores a int value
and a datetime value. Copy the primary key (an identity
column) and converted the value to a datetime on the
insert. Disable or alter all objects, especially triggers,
that use the column meeting_date. Then drop the
meeting_date column and add the column as a datetime
value. Wait for replication to make the change. Update
the new datetime column meeting_date with the converted
datetime value joining on the identity column.
Reestablish all objects and update all applications that
use that the meeting_date column.
Will this work with replication ? Is there a better
way ? etc
We are using merge replication and the column has 200,000
values of a 3,900,000 row table.
It is fairly easy to convert meeting_date value itself to
datetime. I am a developer not a DBA so I do not know all
the nuances of replication.You realize that replication prevents schema changes to tables that are
being replicated. You must drop the publication before you make your
change. Once your change is made, you must re-create the publication and
generate a new snapshot. You stated that this table contains 3.9 million
rows. Are there any other large tables in this publication? What is the
line speed between the publisher and the subscriber(s)? The point that I'm
trying to make here is that the resynchronization could take a very long
time.
How big is the database? Would it be practical to burn it to a CD or DVD
and physically transport it to the subscriber(s)? That might turn out to be
faster than trying to push out a new snapshot.
Also, you might want to investigate using sp_repladdcolumn and
sp_repldropcolumn.
Mike
"Phil396" <anonymous@.discussions.microsoft.com> wrote in message
news:502e01c52328$6d842d10$a601280a@.phx.gbl...
> I need to change a column that is a six character varchar
> called meeting_date to a datetime value. My plan is to
> create a table that stores a int value
> and a datetime value. Copy the primary key (an identity
> column) and converted the value to a datetime on the
> insert. Disable or alter all objects, especially triggers,
> that use the column meeting_date. Then drop the
> meeting_date column and add the column as a datetime
> value. Wait for replication to make the change. Update
> the new datetime column meeting_date with the converted
> datetime value joining on the identity column.
> Reestablish all objects and update all applications that
> use that the meeting_date column.
>
> Will this work with replication ? Is there a better
> way ? etc
> We are using merge replication and the column has 200,000
> values of a 3,900,000 row table.
> It is fairly easy to convert meeting_date value itself to
> datetime. I am a developer not a DBA so I do not know all
> the nuances of replication.
Monday, March 19, 2012
Changed sa password, db maint plan not working
Our in-house developer changed the "sa" password on the Sql 2000 server and now the database maintenance plan that I created for backups to disk isn't working. The day it stopped I received the following errors in the log:
"2003-02-13 09:20:41.37 spid58 Error: 15457, Severity: 0, State: 1
2003-02-13 09:20:41.37 spid58 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..
2003-02-13 09:20:41.48 spid58 Using 'xplog70.dll' version '2000.80.194' to execute extended stored procedure 'xp_msver'.
2003-02-13 15:54:25.59 spid53 Error: 15457, Severity: 0, State: 1
2003-02-13 15:54:25.59 spid53 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..:"I don't think that changing the sa password has got anything to do with the DBMaintenance plan unless you are using DTS for running the DB Backup etc etc.
"2003-02-13 09:20:41.37 spid58 Error: 15457, Severity: 0, State: 1
2003-02-13 09:20:41.37 spid58 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..
2003-02-13 09:20:41.48 spid58 Using 'xplog70.dll' version '2000.80.194' to execute extended stored procedure 'xp_msver'.
2003-02-13 15:54:25.59 spid53 Error: 15457, Severity: 0, State: 1
2003-02-13 15:54:25.59 spid53 Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install..:"I don't think that changing the sa password has got anything to do with the DBMaintenance plan unless you are using DTS for running the DB Backup etc etc.
Thursday, March 8, 2012
Change to behavior in SQL 2005 SP2?
I just applied SQL 2005 SP2 to our SQL 2005 server. We have a maintenance
plan that does a transaction log backup on all databases every hour. Prior
to SP2 (running build 2153), the maintenance plan would silently skip
databases in simple recovery mode. Immediately after applying the service
pack though, the next scheduled job reported an error for every database in
simple recovery mode.
Is this an expected change in behavior with SP2? I did not see anything in
the release notes about this.I've heard various rumblings that you're better off re-creating you
maintenance plans under SP2. The underlying mechanics have been overhauled,
and that just seems like the safest route.
"Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
news:%23HKkjvcVHHA.3592@.TK2MSFTNGP03.phx.gbl...
>I just applied SQL 2005 SP2 to our SQL 2005 server. We have a maintenance
>plan that does a transaction log backup on all databases every hour. Prior
>to SP2 (running build 2153), the maintenance plan would silently skip
>databases in simple recovery mode. Immediately after applying the service
>pack though, the next scheduled job reported an error for every database in
>simple recovery mode.
> Is this an expected change in behavior with SP2? I did not see anything
> in the release notes about this.
>|||I actually tried this, but it didn't seem to help. The plan is very simple:
one Backup Database task, set to transaction log and configured to backup
all user databases.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OCZR7DdVHHA.388@.TK2MSFTNGP04.phx.gbl...
> I've heard various rumblings that you're better off re-creating you
> maintenance plans under SP2. The underlying mechanics have been
> overhauled, and that just seems like the safest route.
>
> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
> news:%23HKkjvcVHHA.3592@.TK2MSFTNGP03.phx.gbl...
>>I just applied SQL 2005 SP2 to our SQL 2005 server. We have a maintenance
>>plan that does a transaction log backup on all databases every hour.
>>Prior to SP2 (running build 2153), the maintenance plan would silently
>>skip databases in simple recovery mode. Immediately after applying the
>>service pack though, the next scheduled job reported an error for every
>>database in simple recovery mode.
>> Is this an expected change in behavior with SP2? I did not see anything
>> in the release notes about this.
>|||Michael,
Could this fix be what changed this:-
http://support.microsoft.com/kb/921003/ ?
We found that you could not create a maintenance plan unless the DB was in
Full recovery mode. If you created a maintenance plan and then the DB was
changed to the Bulk-logged recovery mode the log backup job ran but did not
do anything. This patch fixed both situations and could have caused yours!
Chris
"Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
news:uWZiEIdVHHA.1208@.TK2MSFTNGP03.phx.gbl...
>I actually tried this, but it didn't seem to help. The plan is very
>simple: one Backup Database task, set to transaction log and configured to
>backup all user databases.
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OCZR7DdVHHA.388@.TK2MSFTNGP04.phx.gbl...
>> I've heard various rumblings that you're better off re-creating you
>> maintenance plans under SP2. The underlying mechanics have been
>> overhauled, and that just seems like the safest route.
>>
>> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
>> news:%23HKkjvcVHHA.3592@.TK2MSFTNGP03.phx.gbl...
>>I just applied SQL 2005 SP2 to our SQL 2005 server. We have a
>>maintenance plan that does a transaction log backup on all databases
>>every hour. Prior to SP2 (running build 2153), the maintenance plan would
>>silently skip databases in simple recovery mode. Immediately after
>>applying the service pack though, the next scheduled job reported an
>>error for every database in simple recovery mode.
>> Is this an expected change in behavior with SP2? I did not see anything
>> in the release notes about this.
>>
>|||It does seem possible, although that referse to bulk-logged recovery mode
and not simple.
I suppose my only recourse is to call PSS and see if they have a response
other than "only backup databases in full recovery mode."
I prefer to select "All Databases" in the maintenance plan so I don't have
to remember to add new databases to the plan as they are added.
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:OLswF8pVHHA.4404@.TK2MSFTNGP03.phx.gbl...
> Michael,
> Could this fix be what changed this:-
> http://support.microsoft.com/kb/921003/ ?
> We found that you could not create a maintenance plan unless the DB was in
> Full recovery mode. If you created a maintenance plan and then the DB was
> changed to the Bulk-logged recovery mode the log backup job ran but did
> not do anything. This patch fixed both situations and could have caused
> yours!
> Chris
> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
> news:uWZiEIdVHHA.1208@.TK2MSFTNGP03.phx.gbl...
>>I actually tried this, but it didn't seem to help. The plan is very
>>simple: one Backup Database task, set to transaction log and configured to
>>backup all user databases.
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:OCZR7DdVHHA.388@.TK2MSFTNGP04.phx.gbl...
>> I've heard various rumblings that you're better off re-creating you
>> maintenance plans under SP2. The underlying mechanics have been
>> overhauled, and that just seems like the safest route.
>>
>> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
>> news:%23HKkjvcVHHA.3592@.TK2MSFTNGP03.phx.gbl...
>>I just applied SQL 2005 SP2 to our SQL 2005 server. We have a
>>maintenance plan that does a transaction log backup on all databases
>>every hour. Prior to SP2 (running build 2153), the maintenance plan
>>would silently skip databases in simple recovery mode. Immediately
>>after applying the service pack though, the next scheduled job reported
>>an error for every database in simple recovery mode.
>> Is this an expected change in behavior with SP2? I did not see
>> anything in the release notes about this.
>>
>>
>|||On second thought maybe I can write a script to only do BACKUP LOG on
databases in FULL recovery mode instead of relying on the maintenance plan
to generate the script.
"Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
news:uz8E8y2VHHA.3948@.TK2MSFTNGP05.phx.gbl...
> It does seem possible, although that referse to bulk-logged recovery mode
> and not simple.
> I suppose my only recourse is to call PSS and see if they have a response
> other than "only backup databases in full recovery mode."
> I prefer to select "All Databases" in the maintenance plan so I don't have
> to remember to add new databases to the plan as they are added.
>
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:OLswF8pVHHA.4404@.TK2MSFTNGP03.phx.gbl...
>> Michael,
>> Could this fix be what changed this:-
>> http://support.microsoft.com/kb/921003/ ?
>> We found that you could not create a maintenance plan unless the DB was
>> in Full recovery mode. If you created a maintenance plan and then the DB
>> was changed to the Bulk-logged recovery mode the log backup job ran but
>> did not do anything. This patch fixed both situations and could have
>> caused yours!
>> Chris
>> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
>> news:uWZiEIdVHHA.1208@.TK2MSFTNGP03.phx.gbl...
>>I actually tried this, but it didn't seem to help. The plan is very
>>simple: one Backup Database task, set to transaction log and configured
>>to backup all user databases.
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:OCZR7DdVHHA.388@.TK2MSFTNGP04.phx.gbl...
>> I've heard various rumblings that you're better off re-creating you
>> maintenance plans under SP2. The underlying mechanics have been
>> overhauled, and that just seems like the safest route.
>>
>> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
>> news:%23HKkjvcVHHA.3592@.TK2MSFTNGP03.phx.gbl...
>>I just applied SQL 2005 SP2 to our SQL 2005 server. We have a
>>maintenance plan that does a transaction log backup on all databases
>>every hour. Prior to SP2 (running build 2153), the maintenance plan
>>would silently skip databases in simple recovery mode. Immediately
>>after applying the service pack though, the next scheduled job reported
>>an error for every database in simple recovery mode.
>> Is this an expected change in behavior with SP2? I did not see
>> anything in the release notes about this.
>>
>>
>>
>|||Michael,
The wizard still gives you the choice of All User databases, All System
database and All databases. I would not expect the system ones to change so
create 2 sets of plans, 1 to cover the system ones and one to cover the user
ones.
Chris
"Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
news:uz8E8y2VHHA.3948@.TK2MSFTNGP05.phx.gbl...
> It does seem possible, although that referse to bulk-logged recovery mode
> and not simple.
> I suppose my only recourse is to call PSS and see if they have a response
> other than "only backup databases in full recovery mode."
> I prefer to select "All Databases" in the maintenance plan so I don't have
> to remember to add new databases to the plan as they are added.
>
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:OLswF8pVHHA.4404@.TK2MSFTNGP03.phx.gbl...
>> Michael,
>> Could this fix be what changed this:-
>> http://support.microsoft.com/kb/921003/ ?
>> We found that you could not create a maintenance plan unless the DB was
>> in Full recovery mode. If you created a maintenance plan and then the DB
>> was changed to the Bulk-logged recovery mode the log backup job ran but
>> did not do anything. This patch fixed both situations and could have
>> caused yours!
>> Chris
>> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
>> news:uWZiEIdVHHA.1208@.TK2MSFTNGP03.phx.gbl...
>>I actually tried this, but it didn't seem to help. The plan is very
>>simple: one Backup Database task, set to transaction log and configured
>>to backup all user databases.
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:OCZR7DdVHHA.388@.TK2MSFTNGP04.phx.gbl...
>> I've heard various rumblings that you're better off re-creating you
>> maintenance plans under SP2. The underlying mechanics have been
>> overhauled, and that just seems like the safest route.
>>
>> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
>> news:%23HKkjvcVHHA.3592@.TK2MSFTNGP03.phx.gbl...
>>I just applied SQL 2005 SP2 to our SQL 2005 server. We have a
>>maintenance plan that does a transaction log backup on all databases
>>every hour. Prior to SP2 (running build 2153), the maintenance plan
>>would silently skip databases in simple recovery mode. Immediately
>>after applying the service pack though, the next scheduled job reported
>>an error for every database in simple recovery mode.
>> Is this an expected change in behavior with SP2? I did not see
>> anything in the release notes about this.
>>
>>
>>
>|||On Feb 23, 11:04 am, "Michael D'Angelo"
<nospamnmda...@.phoenixworx.org> wrote:
> It does seem possible, although that referse to bulk-logged recovery mode
> and not simple.
> I suppose my only recourse is to call PSS and see if they have a response
> other than "only backup databases in full recovery mode."
> I prefer to select "All Databases" in the maintenance plan so I don't have
> to remember to add new databases to the plan as they are added.
>
Have a look at this script of mine... It will automate all of your
backups, including the logs, and will pick up new databases
automatically...
http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html|||Actually, even selecting "All user databases" causes the error. I was
hoping not to have to select specific databases.
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:uPILJH3VHHA.3980@.TK2MSFTNGP02.phx.gbl...
> Michael,
> The wizard still gives you the choice of All User databases, All System
> database and All databases. I would not expect the system ones to change
> so create 2 sets of plans, 1 to cover the system ones and one to cover the
> user ones.
> Chris
> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
> news:uz8E8y2VHHA.3948@.TK2MSFTNGP05.phx.gbl...
>> It does seem possible, although that referse to bulk-logged recovery mode
>> and not simple.
>> I suppose my only recourse is to call PSS and see if they have a response
>> other than "only backup databases in full recovery mode."
>> I prefer to select "All Databases" in the maintenance plan so I don't
>> have to remember to add new databases to the plan as they are added.
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:OLswF8pVHHA.4404@.TK2MSFTNGP03.phx.gbl...
>> Michael,
>> Could this fix be what changed this:-
>> http://support.microsoft.com/kb/921003/ ?
>> We found that you could not create a maintenance plan unless the DB was
>> in Full recovery mode. If you created a maintenance plan and then the DB
>> was changed to the Bulk-logged recovery mode the log backup job ran but
>> did not do anything. This patch fixed both situations and could have
>> caused yours!
>> Chris
>> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
>> news:uWZiEIdVHHA.1208@.TK2MSFTNGP03.phx.gbl...
>>I actually tried this, but it didn't seem to help. The plan is very
>>simple: one Backup Database task, set to transaction log and configured
>>to backup all user databases.
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:OCZR7DdVHHA.388@.TK2MSFTNGP04.phx.gbl...
>> I've heard various rumblings that you're better off re-creating you
>> maintenance plans under SP2. The underlying mechanics have been
>> overhauled, and that just seems like the safest route.
>>
>> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
>> news:%23HKkjvcVHHA.3592@.TK2MSFTNGP03.phx.gbl...
>>I just applied SQL 2005 SP2 to our SQL 2005 server. We have a
>>maintenance plan that does a transaction log backup on all databases
>>every hour. Prior to SP2 (running build 2153), the maintenance plan
>>would silently skip databases in simple recovery mode. Immediately
>>after applying the service pack though, the next scheduled job
>>reported an error for every database in simple recovery mode.
>> Is this an expected change in behavior with SP2? I did not see
>> anything in the release notes about this.
>>
>>
>>
>>
>|||Thanks I'll take a look!
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1172254771.872080.170760@.v33g2000cwv.googlegroups.com...
> On Feb 23, 11:04 am, "Michael D'Angelo"
> <nospamnmda...@.phoenixworx.org> wrote:
>> It does seem possible, although that referse to bulk-logged recovery mode
>> and not simple.
>> I suppose my only recourse is to call PSS and see if they have a response
>> other than "only backup databases in full recovery mode."
>> I prefer to select "All Databases" in the maintenance plan so I don't
>> have
>> to remember to add new databases to the plan as they are added.
> Have a look at this script of mine... It will automate all of your
> backups, including the logs, and will pick up new databases
> automatically...
> http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html
>
plan that does a transaction log backup on all databases every hour. Prior
to SP2 (running build 2153), the maintenance plan would silently skip
databases in simple recovery mode. Immediately after applying the service
pack though, the next scheduled job reported an error for every database in
simple recovery mode.
Is this an expected change in behavior with SP2? I did not see anything in
the release notes about this.I've heard various rumblings that you're better off re-creating you
maintenance plans under SP2. The underlying mechanics have been overhauled,
and that just seems like the safest route.
"Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
news:%23HKkjvcVHHA.3592@.TK2MSFTNGP03.phx.gbl...
>I just applied SQL 2005 SP2 to our SQL 2005 server. We have a maintenance
>plan that does a transaction log backup on all databases every hour. Prior
>to SP2 (running build 2153), the maintenance plan would silently skip
>databases in simple recovery mode. Immediately after applying the service
>pack though, the next scheduled job reported an error for every database in
>simple recovery mode.
> Is this an expected change in behavior with SP2? I did not see anything
> in the release notes about this.
>|||I actually tried this, but it didn't seem to help. The plan is very simple:
one Backup Database task, set to transaction log and configured to backup
all user databases.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OCZR7DdVHHA.388@.TK2MSFTNGP04.phx.gbl...
> I've heard various rumblings that you're better off re-creating you
> maintenance plans under SP2. The underlying mechanics have been
> overhauled, and that just seems like the safest route.
>
> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
> news:%23HKkjvcVHHA.3592@.TK2MSFTNGP03.phx.gbl...
>>I just applied SQL 2005 SP2 to our SQL 2005 server. We have a maintenance
>>plan that does a transaction log backup on all databases every hour.
>>Prior to SP2 (running build 2153), the maintenance plan would silently
>>skip databases in simple recovery mode. Immediately after applying the
>>service pack though, the next scheduled job reported an error for every
>>database in simple recovery mode.
>> Is this an expected change in behavior with SP2? I did not see anything
>> in the release notes about this.
>|||Michael,
Could this fix be what changed this:-
http://support.microsoft.com/kb/921003/ ?
We found that you could not create a maintenance plan unless the DB was in
Full recovery mode. If you created a maintenance plan and then the DB was
changed to the Bulk-logged recovery mode the log backup job ran but did not
do anything. This patch fixed both situations and could have caused yours!
Chris
"Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
news:uWZiEIdVHHA.1208@.TK2MSFTNGP03.phx.gbl...
>I actually tried this, but it didn't seem to help. The plan is very
>simple: one Backup Database task, set to transaction log and configured to
>backup all user databases.
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OCZR7DdVHHA.388@.TK2MSFTNGP04.phx.gbl...
>> I've heard various rumblings that you're better off re-creating you
>> maintenance plans under SP2. The underlying mechanics have been
>> overhauled, and that just seems like the safest route.
>>
>> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
>> news:%23HKkjvcVHHA.3592@.TK2MSFTNGP03.phx.gbl...
>>I just applied SQL 2005 SP2 to our SQL 2005 server. We have a
>>maintenance plan that does a transaction log backup on all databases
>>every hour. Prior to SP2 (running build 2153), the maintenance plan would
>>silently skip databases in simple recovery mode. Immediately after
>>applying the service pack though, the next scheduled job reported an
>>error for every database in simple recovery mode.
>> Is this an expected change in behavior with SP2? I did not see anything
>> in the release notes about this.
>>
>|||It does seem possible, although that referse to bulk-logged recovery mode
and not simple.
I suppose my only recourse is to call PSS and see if they have a response
other than "only backup databases in full recovery mode."
I prefer to select "All Databases" in the maintenance plan so I don't have
to remember to add new databases to the plan as they are added.
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:OLswF8pVHHA.4404@.TK2MSFTNGP03.phx.gbl...
> Michael,
> Could this fix be what changed this:-
> http://support.microsoft.com/kb/921003/ ?
> We found that you could not create a maintenance plan unless the DB was in
> Full recovery mode. If you created a maintenance plan and then the DB was
> changed to the Bulk-logged recovery mode the log backup job ran but did
> not do anything. This patch fixed both situations and could have caused
> yours!
> Chris
> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
> news:uWZiEIdVHHA.1208@.TK2MSFTNGP03.phx.gbl...
>>I actually tried this, but it didn't seem to help. The plan is very
>>simple: one Backup Database task, set to transaction log and configured to
>>backup all user databases.
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:OCZR7DdVHHA.388@.TK2MSFTNGP04.phx.gbl...
>> I've heard various rumblings that you're better off re-creating you
>> maintenance plans under SP2. The underlying mechanics have been
>> overhauled, and that just seems like the safest route.
>>
>> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
>> news:%23HKkjvcVHHA.3592@.TK2MSFTNGP03.phx.gbl...
>>I just applied SQL 2005 SP2 to our SQL 2005 server. We have a
>>maintenance plan that does a transaction log backup on all databases
>>every hour. Prior to SP2 (running build 2153), the maintenance plan
>>would silently skip databases in simple recovery mode. Immediately
>>after applying the service pack though, the next scheduled job reported
>>an error for every database in simple recovery mode.
>> Is this an expected change in behavior with SP2? I did not see
>> anything in the release notes about this.
>>
>>
>|||On second thought maybe I can write a script to only do BACKUP LOG on
databases in FULL recovery mode instead of relying on the maintenance plan
to generate the script.
"Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
news:uz8E8y2VHHA.3948@.TK2MSFTNGP05.phx.gbl...
> It does seem possible, although that referse to bulk-logged recovery mode
> and not simple.
> I suppose my only recourse is to call PSS and see if they have a response
> other than "only backup databases in full recovery mode."
> I prefer to select "All Databases" in the maintenance plan so I don't have
> to remember to add new databases to the plan as they are added.
>
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:OLswF8pVHHA.4404@.TK2MSFTNGP03.phx.gbl...
>> Michael,
>> Could this fix be what changed this:-
>> http://support.microsoft.com/kb/921003/ ?
>> We found that you could not create a maintenance plan unless the DB was
>> in Full recovery mode. If you created a maintenance plan and then the DB
>> was changed to the Bulk-logged recovery mode the log backup job ran but
>> did not do anything. This patch fixed both situations and could have
>> caused yours!
>> Chris
>> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
>> news:uWZiEIdVHHA.1208@.TK2MSFTNGP03.phx.gbl...
>>I actually tried this, but it didn't seem to help. The plan is very
>>simple: one Backup Database task, set to transaction log and configured
>>to backup all user databases.
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:OCZR7DdVHHA.388@.TK2MSFTNGP04.phx.gbl...
>> I've heard various rumblings that you're better off re-creating you
>> maintenance plans under SP2. The underlying mechanics have been
>> overhauled, and that just seems like the safest route.
>>
>> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
>> news:%23HKkjvcVHHA.3592@.TK2MSFTNGP03.phx.gbl...
>>I just applied SQL 2005 SP2 to our SQL 2005 server. We have a
>>maintenance plan that does a transaction log backup on all databases
>>every hour. Prior to SP2 (running build 2153), the maintenance plan
>>would silently skip databases in simple recovery mode. Immediately
>>after applying the service pack though, the next scheduled job reported
>>an error for every database in simple recovery mode.
>> Is this an expected change in behavior with SP2? I did not see
>> anything in the release notes about this.
>>
>>
>>
>|||Michael,
The wizard still gives you the choice of All User databases, All System
database and All databases. I would not expect the system ones to change so
create 2 sets of plans, 1 to cover the system ones and one to cover the user
ones.
Chris
"Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
news:uz8E8y2VHHA.3948@.TK2MSFTNGP05.phx.gbl...
> It does seem possible, although that referse to bulk-logged recovery mode
> and not simple.
> I suppose my only recourse is to call PSS and see if they have a response
> other than "only backup databases in full recovery mode."
> I prefer to select "All Databases" in the maintenance plan so I don't have
> to remember to add new databases to the plan as they are added.
>
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:OLswF8pVHHA.4404@.TK2MSFTNGP03.phx.gbl...
>> Michael,
>> Could this fix be what changed this:-
>> http://support.microsoft.com/kb/921003/ ?
>> We found that you could not create a maintenance plan unless the DB was
>> in Full recovery mode. If you created a maintenance plan and then the DB
>> was changed to the Bulk-logged recovery mode the log backup job ran but
>> did not do anything. This patch fixed both situations and could have
>> caused yours!
>> Chris
>> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
>> news:uWZiEIdVHHA.1208@.TK2MSFTNGP03.phx.gbl...
>>I actually tried this, but it didn't seem to help. The plan is very
>>simple: one Backup Database task, set to transaction log and configured
>>to backup all user databases.
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:OCZR7DdVHHA.388@.TK2MSFTNGP04.phx.gbl...
>> I've heard various rumblings that you're better off re-creating you
>> maintenance plans under SP2. The underlying mechanics have been
>> overhauled, and that just seems like the safest route.
>>
>> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
>> news:%23HKkjvcVHHA.3592@.TK2MSFTNGP03.phx.gbl...
>>I just applied SQL 2005 SP2 to our SQL 2005 server. We have a
>>maintenance plan that does a transaction log backup on all databases
>>every hour. Prior to SP2 (running build 2153), the maintenance plan
>>would silently skip databases in simple recovery mode. Immediately
>>after applying the service pack though, the next scheduled job reported
>>an error for every database in simple recovery mode.
>> Is this an expected change in behavior with SP2? I did not see
>> anything in the release notes about this.
>>
>>
>>
>|||On Feb 23, 11:04 am, "Michael D'Angelo"
<nospamnmda...@.phoenixworx.org> wrote:
> It does seem possible, although that referse to bulk-logged recovery mode
> and not simple.
> I suppose my only recourse is to call PSS and see if they have a response
> other than "only backup databases in full recovery mode."
> I prefer to select "All Databases" in the maintenance plan so I don't have
> to remember to add new databases to the plan as they are added.
>
Have a look at this script of mine... It will automate all of your
backups, including the logs, and will pick up new databases
automatically...
http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html|||Actually, even selecting "All user databases" causes the error. I was
hoping not to have to select specific databases.
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:uPILJH3VHHA.3980@.TK2MSFTNGP02.phx.gbl...
> Michael,
> The wizard still gives you the choice of All User databases, All System
> database and All databases. I would not expect the system ones to change
> so create 2 sets of plans, 1 to cover the system ones and one to cover the
> user ones.
> Chris
> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
> news:uz8E8y2VHHA.3948@.TK2MSFTNGP05.phx.gbl...
>> It does seem possible, although that referse to bulk-logged recovery mode
>> and not simple.
>> I suppose my only recourse is to call PSS and see if they have a response
>> other than "only backup databases in full recovery mode."
>> I prefer to select "All Databases" in the maintenance plan so I don't
>> have to remember to add new databases to the plan as they are added.
>>
>> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
>> news:OLswF8pVHHA.4404@.TK2MSFTNGP03.phx.gbl...
>> Michael,
>> Could this fix be what changed this:-
>> http://support.microsoft.com/kb/921003/ ?
>> We found that you could not create a maintenance plan unless the DB was
>> in Full recovery mode. If you created a maintenance plan and then the DB
>> was changed to the Bulk-logged recovery mode the log backup job ran but
>> did not do anything. This patch fixed both situations and could have
>> caused yours!
>> Chris
>> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
>> news:uWZiEIdVHHA.1208@.TK2MSFTNGP03.phx.gbl...
>>I actually tried this, but it didn't seem to help. The plan is very
>>simple: one Backup Database task, set to transaction log and configured
>>to backup all user databases.
>> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
>> message news:OCZR7DdVHHA.388@.TK2MSFTNGP04.phx.gbl...
>> I've heard various rumblings that you're better off re-creating you
>> maintenance plans under SP2. The underlying mechanics have been
>> overhauled, and that just seems like the safest route.
>>
>> "Michael D'Angelo" <nospamnmdange@.phoenixworx.org> wrote in message
>> news:%23HKkjvcVHHA.3592@.TK2MSFTNGP03.phx.gbl...
>>I just applied SQL 2005 SP2 to our SQL 2005 server. We have a
>>maintenance plan that does a transaction log backup on all databases
>>every hour. Prior to SP2 (running build 2153), the maintenance plan
>>would silently skip databases in simple recovery mode. Immediately
>>after applying the service pack though, the next scheduled job
>>reported an error for every database in simple recovery mode.
>> Is this an expected change in behavior with SP2? I did not see
>> anything in the release notes about this.
>>
>>
>>
>>
>|||Thanks I'll take a look!
"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1172254771.872080.170760@.v33g2000cwv.googlegroups.com...
> On Feb 23, 11:04 am, "Michael D'Angelo"
> <nospamnmda...@.phoenixworx.org> wrote:
>> It does seem possible, although that referse to bulk-logged recovery mode
>> and not simple.
>> I suppose my only recourse is to call PSS and see if they have a response
>> other than "only backup databases in full recovery mode."
>> I prefer to select "All Databases" in the maintenance plan so I don't
>> have
>> to remember to add new databases to the plan as they are added.
> Have a look at this script of mine... It will automate all of your
> backups, including the logs, and will pick up new databases
> automatically...
> http://realsqlguy.blogspot.com/2007/02/automating-database-backups.html
>
Tuesday, February 14, 2012
Change owner of Maintenance Plan
I know that you can change the owner of a job. How can I change the owner of
a maintenance plan?
As a follow up question, does it really matter from a technical or
functional perspective who owns the maintenance plan. The job owner can be
different and has technical implications per its owner. One of the reasons I
want to change the owner, is because the person that created these plans is
not longer in the role of managing databases. I would like to change the
owner to a generic account like 'sa' or to an appropriate active dba
individual.
Thanks
TimHi Tim,
A maintained plan is executed by means of SQL Server Job, so if you
change the owner in the Job it will reflect accordingly. An owner is
important because SQL Server Job needs proper authorization to expect steps
layer out in the plan/job.
Mohit K. Gupta
MCTS: SQL Server 2005
"timw86" wrote:
> I know that you can change the owner of a job. How can I change the owner of
> a maintenance plan?
> As a follow up question, does it really matter from a technical or
> functional perspective who owns the maintenance plan. The job owner can be
> different and has technical implications per its owner. One of the reasons I
> want to change the owner, is because the person that created these plans is
> not longer in the role of managing databases. I would like to change the
> owner to a generic account like 'sa' or to an appropriate active dba
> individual.
> Thanks
> Tim|||I don't believe you can - not in a support manner anyway.
And no...It doesn't matter. It's the job owner that matters.
-Sue
On Wed, 20 Jun 2007 06:11:01 -0700, timw86
<timw86@.discussions.microsoft.com> wrote:
>I know that you can change the owner of a job. How can I change the owner of
>a maintenance plan?
>As a follow up question, does it really matter from a technical or
>functional perspective who owns the maintenance plan. The job owner can be
>different and has technical implications per its owner. One of the reasons I
>want to change the owner, is because the person that created these plans is
>not longer in the role of managing databases. I would like to change the
>owner to a generic account like 'sa' or to an appropriate active dba
>individual.
>Thanks
>Tim
a maintenance plan?
As a follow up question, does it really matter from a technical or
functional perspective who owns the maintenance plan. The job owner can be
different and has technical implications per its owner. One of the reasons I
want to change the owner, is because the person that created these plans is
not longer in the role of managing databases. I would like to change the
owner to a generic account like 'sa' or to an appropriate active dba
individual.
Thanks
TimHi Tim,
A maintained plan is executed by means of SQL Server Job, so if you
change the owner in the Job it will reflect accordingly. An owner is
important because SQL Server Job needs proper authorization to expect steps
layer out in the plan/job.
Mohit K. Gupta
MCTS: SQL Server 2005
"timw86" wrote:
> I know that you can change the owner of a job. How can I change the owner of
> a maintenance plan?
> As a follow up question, does it really matter from a technical or
> functional perspective who owns the maintenance plan. The job owner can be
> different and has technical implications per its owner. One of the reasons I
> want to change the owner, is because the person that created these plans is
> not longer in the role of managing databases. I would like to change the
> owner to a generic account like 'sa' or to an appropriate active dba
> individual.
> Thanks
> Tim|||I don't believe you can - not in a support manner anyway.
And no...It doesn't matter. It's the job owner that matters.
-Sue
On Wed, 20 Jun 2007 06:11:01 -0700, timw86
<timw86@.discussions.microsoft.com> wrote:
>I know that you can change the owner of a job. How can I change the owner of
>a maintenance plan?
>As a follow up question, does it really matter from a technical or
>functional perspective who owns the maintenance plan. The job owner can be
>different and has technical implications per its owner. One of the reasons I
>want to change the owner, is because the person that created these plans is
>not longer in the role of managing databases. I would like to change the
>owner to a generic account like 'sa' or to an appropriate active dba
>individual.
>Thanks
>Tim
Change owner of Maintenance Plan
I know that you can change the owner of a job. How can I change the owner of
a maintenance plan?
As a follow up question, does it really matter from a technical or
functional perspective who owns the maintenance plan. The job owner can be
different and has technical implications per its owner. One of the reasons I
want to change the owner, is because the person that created these plans is
not longer in the role of managing databases. I would like to change the
owner to a generic account like 'sa' or to an appropriate active dba
individual.
Thanks
Tim
Hi Tim,
A maintained plan is executed by means of SQL Server Job, so if you
change the owner in the Job it will reflect accordingly. An owner is
important because SQL Server Job needs proper authorization to expect steps
layer out in the plan/job.
Mohit K. Gupta
MCTS: SQL Server 2005
"timw86" wrote:
> I know that you can change the owner of a job. How can I change the owner of
> a maintenance plan?
> As a follow up question, does it really matter from a technical or
> functional perspective who owns the maintenance plan. The job owner can be
> different and has technical implications per its owner. One of the reasons I
> want to change the owner, is because the person that created these plans is
> not longer in the role of managing databases. I would like to change the
> owner to a generic account like 'sa' or to an appropriate active dba
> individual.
> Thanks
> Tim
|||I don't believe you can - not in a support manner anyway.
And no...It doesn't matter. It's the job owner that matters.
-Sue
On Wed, 20 Jun 2007 06:11:01 -0700, timw86
<timw86@.discussions.microsoft.com> wrote:
>I know that you can change the owner of a job. How can I change the owner of
>a maintenance plan?
>As a follow up question, does it really matter from a technical or
>functional perspective who owns the maintenance plan. The job owner can be
>different and has technical implications per its owner. One of the reasons I
>want to change the owner, is because the person that created these plans is
>not longer in the role of managing databases. I would like to change the
>owner to a generic account like 'sa' or to an appropriate active dba
>individual.
>Thanks
>Tim
a maintenance plan?
As a follow up question, does it really matter from a technical or
functional perspective who owns the maintenance plan. The job owner can be
different and has technical implications per its owner. One of the reasons I
want to change the owner, is because the person that created these plans is
not longer in the role of managing databases. I would like to change the
owner to a generic account like 'sa' or to an appropriate active dba
individual.
Thanks
Tim
Hi Tim,
A maintained plan is executed by means of SQL Server Job, so if you
change the owner in the Job it will reflect accordingly. An owner is
important because SQL Server Job needs proper authorization to expect steps
layer out in the plan/job.
Mohit K. Gupta
MCTS: SQL Server 2005
"timw86" wrote:
> I know that you can change the owner of a job. How can I change the owner of
> a maintenance plan?
> As a follow up question, does it really matter from a technical or
> functional perspective who owns the maintenance plan. The job owner can be
> different and has technical implications per its owner. One of the reasons I
> want to change the owner, is because the person that created these plans is
> not longer in the role of managing databases. I would like to change the
> owner to a generic account like 'sa' or to an appropriate active dba
> individual.
> Thanks
> Tim
|||I don't believe you can - not in a support manner anyway.
And no...It doesn't matter. It's the job owner that matters.
-Sue
On Wed, 20 Jun 2007 06:11:01 -0700, timw86
<timw86@.discussions.microsoft.com> wrote:
>I know that you can change the owner of a job. How can I change the owner of
>a maintenance plan?
>As a follow up question, does it really matter from a technical or
>functional perspective who owns the maintenance plan. The job owner can be
>different and has technical implications per its owner. One of the reasons I
>want to change the owner, is because the person that created these plans is
>not longer in the role of managing databases. I would like to change the
>owner to a generic account like 'sa' or to an appropriate active dba
>individual.
>Thanks
>Tim
Change owner of Maintenance Plan
I know that you can change the owner of a job. How can I change the owner o
f
a maintenance plan?
As a follow up question, does it really matter from a technical or
functional perspective who owns the maintenance plan. The job owner can be
different and has technical implications per its owner. One of the reasons
I
want to change the owner, is because the person that created these plans is
not longer in the role of managing databases. I would like to change the
owner to a generic account like 'sa' or to an appropriate active dba
individual.
Thanks
TimHi Tim,
A maintained plan is executed by means of SQL Server Job, so if you
change the owner in the Job it will reflect accordingly. An owner is
important because SQL Server Job needs proper authorization to expect steps
layer out in the plan/job.
Mohit K. Gupta
MCTS: SQL Server 2005
"timw86" wrote:
> I know that you can change the owner of a job. How can I change the owner
of
> a maintenance plan?
> As a follow up question, does it really matter from a technical or
> functional perspective who owns the maintenance plan. The job owner can b
e
> different and has technical implications per its owner. One of the reason
s I
> want to change the owner, is because the person that created these plans i
s
> not longer in the role of managing databases. I would like to change the
> owner to a generic account like 'sa' or to an appropriate active dba
> individual.
> Thanks
> Tim|||I don't believe you can - not in a support manner anyway.
And no...It doesn't matter. It's the job owner that matters.
-Sue
On Wed, 20 Jun 2007 06:11:01 -0700, timw86
<timw86@.discussions.microsoft.com> wrote:
>I know that you can change the owner of a job. How can I change the owner
of
>a maintenance plan?
>As a follow up question, does it really matter from a technical or
>functional perspective who owns the maintenance plan. The job owner can be
>different and has technical implications per its owner. One of the reasons
I
>want to change the owner, is because the person that created these plans is
>not longer in the role of managing databases. I would like to change the
>owner to a generic account like 'sa' or to an appropriate active dba
>individual.
>Thanks
>Tim
f
a maintenance plan?
As a follow up question, does it really matter from a technical or
functional perspective who owns the maintenance plan. The job owner can be
different and has technical implications per its owner. One of the reasons
I
want to change the owner, is because the person that created these plans is
not longer in the role of managing databases. I would like to change the
owner to a generic account like 'sa' or to an appropriate active dba
individual.
Thanks
TimHi Tim,
A maintained plan is executed by means of SQL Server Job, so if you
change the owner in the Job it will reflect accordingly. An owner is
important because SQL Server Job needs proper authorization to expect steps
layer out in the plan/job.
Mohit K. Gupta
MCTS: SQL Server 2005
"timw86" wrote:
> I know that you can change the owner of a job. How can I change the owner
of
> a maintenance plan?
> As a follow up question, does it really matter from a technical or
> functional perspective who owns the maintenance plan. The job owner can b
e
> different and has technical implications per its owner. One of the reason
s I
> want to change the owner, is because the person that created these plans i
s
> not longer in the role of managing databases. I would like to change the
> owner to a generic account like 'sa' or to an appropriate active dba
> individual.
> Thanks
> Tim|||I don't believe you can - not in a support manner anyway.
And no...It doesn't matter. It's the job owner that matters.
-Sue
On Wed, 20 Jun 2007 06:11:01 -0700, timw86
<timw86@.discussions.microsoft.com> wrote:
>I know that you can change the owner of a job. How can I change the owner
of
>a maintenance plan?
>As a follow up question, does it really matter from a technical or
>functional perspective who owns the maintenance plan. The job owner can be
>different and has technical implications per its owner. One of the reasons
I
>want to change the owner, is because the person that created these plans is
>not longer in the role of managing databases. I would like to change the
>owner to a generic account like 'sa' or to an appropriate active dba
>individual.
>Thanks
>Tim
Sunday, February 12, 2012
Change Owner in Database Maintenance Plan
Who do you change the database owner in Database Maintenance Plan that was
created with SQL Server 2005?
Thank You,Hi,
As far as I know you cant change the database maintenance plan owner; but
you could change the job owner.
Thanks
Hari
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E22412B0-D621-48BC-B4A0-8B8A723703B4@.microsoft.com...
> Who do you change the database owner in Database Maintenance Plan that was
> created with SQL Server 2005?
> Thank You,
created with SQL Server 2005?
Thank You,Hi,
As far as I know you cant change the database maintenance plan owner; but
you could change the job owner.
Thanks
Hari
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E22412B0-D621-48BC-B4A0-8B8A723703B4@.microsoft.com...
> Who do you change the database owner in Database Maintenance Plan that was
> created with SQL Server 2005?
> Thank You,
Change Owner in Database Maintenance Plan
Who do you change the database owner in Database Maintenance Plan that was
created with SQL Server 2005?
Thank You,
Hi,
As far as I know you cant change the database maintenance plan owner; but
you could change the job owner.
Thanks
Hari
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E22412B0-D621-48BC-B4A0-8B8A723703B4@.microsoft.com...
> Who do you change the database owner in Database Maintenance Plan that was
> created with SQL Server 2005?
> Thank You,
created with SQL Server 2005?
Thank You,
Hi,
As far as I know you cant change the database maintenance plan owner; but
you could change the job owner.
Thanks
Hari
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E22412B0-D621-48BC-B4A0-8B8A723703B4@.microsoft.com...
> Who do you change the database owner in Database Maintenance Plan that was
> created with SQL Server 2005?
> Thank You,
Change Owner in Database Maintenance Plan
Who do you change the database owner in Database Maintenance Plan that was
created with SQL Server 2005?
Thank You,Hi,
As far as I know you cant change the database maintenance plan owner; but
you could change the job owner.
Thanks
Hari
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E22412B0-D621-48BC-B4A0-8B8A723703B4@.microsoft.com...
> Who do you change the database owner in Database Maintenance Plan that was
> created with SQL Server 2005?
> Thank You,
created with SQL Server 2005?
Thank You,Hi,
As far as I know you cant change the database maintenance plan owner; but
you could change the job owner.
Thanks
Hari
SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:E22412B0-D621-48BC-B4A0-8B8A723703B4@.microsoft.com...
> Who do you change the database owner in Database Maintenance Plan that was
> created with SQL Server 2005?
> Thank You,
Change of Execution plan
Hi,
We migrated our database from SQL Server 2000 to Yukon last week. Now, when we run our application it has slowed down. We analyzed some stored procedure and they seems to have degarded. The execution plan has changed. Now, this looks like there's lot of work if we have to tune each query w.r.t the new execution plan. Our application has around 4000 stored procs. Is anyone aware of some generic pattern or solution such that these exection plans problem can resolved? Also, does the new execution plan ensure that the once we tune stored procs will perform better than SQL Server 2000.
Need help on this, otherwise it seems we might have to move back to 2000.
Thanks in Advance
Ritesh
Yes we do updated the statistics and recomplied the SP. In few queries we changed Not Exists to Left Outer Join and this works well. I don't say that SQL Server 2005 comes up with less efficient query plan, may be it comes up with much better execution however it looks like to make queries work w.r.t to those plans we might need to do some query tuning.
Change of Execution plan
Hi,
We migrated our database from SQL Server 2000 to Yukon last week. Now, when we run our application it has slowed down. We analyzed some stored procedure and they seems to have degarded. The execution plan has changed. Now, this looks like there's lot of work if we have to tune each query w.r.t the new execution plan. Our application has around 4000 stored procs. Is anyone aware of some generic pattern or solution such that these exection plans problem can resolved? Also, does the new execution plan ensure that the once we tune stored procs will perform better than SQL Server 2000.
Need help on this, otherwise it seems we might have to move back to 2000.
Thanks in Advance
Ritesh
Yes we do updated the statistics and recomplied the SP. In few queries we changed Not Exists to Left Outer Join and this works well. I don't say that SQL Server 2005 comes up with less efficient query plan, may be it comes up with much better execution however it looks like to make queries work w.r.t to those plans we might need to do some query tuning.
Friday, February 10, 2012
Change maintenance plan owner
I would like to change the owner of several maintenance plans. I can't seem
to find a way to that without recreating each plan while connected as the new
owner. Is there a method similar to changing a job's owner via the job's
properties or a database's by sp_changedbowner?
ThanksJim,
Sorry, I don't know a neat way to change the maintenance plan owner. (In
SQL Server 2000, you can go through the process to modify system tables and
change the 'owner' column of msdb.dbo.sysdbmaintplans (although I don't
recommend it) but in SQL Server 2005 that avenue has been closed.)
You can, of course, change the job owner that runs the maintenance plan as
you already described. Is there a reason that the maintenance plan owner
needs to be changed? What side effects are you experiencing?
RLF
"Jim Early" <JimEarly@.discussions.microsoft.com> wrote in message
news:DF28A436-C2D0-4F86-9BC7-5591A164A464@.microsoft.com...
>I would like to change the owner of several maintenance plans. I can't
>seem
> to find a way to that without recreating each plan while connected as the
> new
> owner. Is there a method similar to changing a job's owner via the job's
> properties or a database's by sp_changedbowner?
> Thanks|||Changing the owner of the job only works until the maintenance plan is
modified, then the jobs reverts back to the owner of the maintenance plan.
You might review this article:
http://sqlblogcasts.com/blogs/acalvett/archive/2007/12/30/the-job-whose-owner-kept-coming-back.aspx
"Russell Fields" wrote:
> Jim,
> Sorry, I don't know a neat way to change the maintenance plan owner. (In
> SQL Server 2000, you can go through the process to modify system tables and
> change the 'owner' column of msdb.dbo.sysdbmaintplans (although I don't
> recommend it) but in SQL Server 2005 that avenue has been closed.)
> You can, of course, change the job owner that runs the maintenance plan as
> you already described. Is there a reason that the maintenance plan owner
> needs to be changed? What side effects are you experiencing?
> RLF
> "Jim Early" <JimEarly@.discussions.microsoft.com> wrote in message
> news:DF28A436-C2D0-4F86-9BC7-5591A164A464@.microsoft.com...
> >I would like to change the owner of several maintenance plans. I can't
> >seem
> > to find a way to that without recreating each plan while connected as the
> > new
> > owner. Is there a method similar to changing a job's owner via the job's
> > properties or a database's by sp_changedbowner?
> >
> > Thanks
>
>|||Interesting. Thanks, RLF
"jed" <jed@.discussions.microsoft.com> wrote in message
news:96DC37A2-DD29-4EE2-A89D-F24A8DC41981@.microsoft.com...
> Changing the owner of the job only works until the maintenance plan is
> modified, then the jobs reverts back to the owner of the maintenance plan.
> You might review this article:
> http://sqlblogcasts.com/blogs/acalvett/archive/2007/12/30/the-job-whose-owner-kept-coming-back.aspx
> "Russell Fields" wrote:
>> Jim,
>> Sorry, I don't know a neat way to change the maintenance plan owner. (In
>> SQL Server 2000, you can go through the process to modify system tables
>> and
>> change the 'owner' column of msdb.dbo.sysdbmaintplans (although I don't
>> recommend it) but in SQL Server 2005 that avenue has been closed.)
>> You can, of course, change the job owner that runs the maintenance plan
>> as
>> you already described. Is there a reason that the maintenance plan owner
>> needs to be changed? What side effects are you experiencing?
>> RLF
>> "Jim Early" <JimEarly@.discussions.microsoft.com> wrote in message
>> news:DF28A436-C2D0-4F86-9BC7-5591A164A464@.microsoft.com...
>> >I would like to change the owner of several maintenance plans. I can't
>> >seem
>> > to find a way to that without recreating each plan while connected as
>> > the
>> > new
>> > owner. Is there a method similar to changing a job's owner via the
>> > job's
>> > properties or a database's by sp_changedbowner?
>> >
>> > Thanks
>>|||I encountered the same issue with the Maintenance Plan, when I made a change
to the MaintPlan, the Job changed to that of the owner of the MaintPlan.
According to some reading I have been doing, there really isnâ't a way to
change the Maintenance Plan Owner yet, I do understand why the MaintPlan
owner needs to be changed. In my case, the old DBA has been moved to another
section and no longer have rights to the SQL Server yet, when a change is
made to the MaintPlan, the job defaults to his name and the job fails. Also,
if the person is no longer an employee of the company yet, he created the
plans under his account, the MaintPlan defaults to his account or the account
he used when in SQL.
What I did to change the Owner of all Maintenance Plans in SQL 2005 was, if
you use windows authentication in SQL â'run asâ' the SQL account that is the
manager of SQL or connect to SQL using the SQL Account that manages your SQL.
From there, modify the Maintenance Plan, such as rename it, then rename it
back; the MaintPlan will assume the account you are using as the â'ownerâ'.
This may be tedious but it worksâ?¦
"Jim Early" wrote:
> I would like to change the owner of several maintenance plans. I can't seem
> to find a way to that without recreating each plan while connected as the new
> owner. Is there a method similar to changing a job's owner via the job's
> properties or a database's by sp_changedbowner?
> Thanks|||/*Here's how to change the owner of a maintenance plan to dbo in SQL Server
2005*/
--to find the name and owner of the maintenance plan
--select * from msdb.dbo.sysdtspackages90
--to find the sid you want to use for the new owner
--select * from sysusers
UPDATE
[msdb].[dbo].[sysdtspackages90]
SET
[ownersid] = 0x01
WHERE
[name] = 'MaintenancePlan'|||/*Here's how to change the owner of a maintenance plan to dbo in SQL Server
2005*/
--to find the name and owner of the maintenance plan
--select * from msdb.dbo.sysdtspackages90
--to find the sid you want to use for the new owner
--select * from sysusers
UPDATE
[msdb].[dbo].[sysdtspackages90]
SET
[ownersid] = 0x01
WHERE
[name] = 'MaintenancePlan'
"Jim Early" wrote:
> I would like to change the owner of several maintenance plans. I can't seem
> to find a way to that without recreating each plan while connected as the new
> owner. Is there a method similar to changing a job's owner via the job's
> properties or a database's by sp_changedbowner?
> Thanks
to find a way to that without recreating each plan while connected as the new
owner. Is there a method similar to changing a job's owner via the job's
properties or a database's by sp_changedbowner?
ThanksJim,
Sorry, I don't know a neat way to change the maintenance plan owner. (In
SQL Server 2000, you can go through the process to modify system tables and
change the 'owner' column of msdb.dbo.sysdbmaintplans (although I don't
recommend it) but in SQL Server 2005 that avenue has been closed.)
You can, of course, change the job owner that runs the maintenance plan as
you already described. Is there a reason that the maintenance plan owner
needs to be changed? What side effects are you experiencing?
RLF
"Jim Early" <JimEarly@.discussions.microsoft.com> wrote in message
news:DF28A436-C2D0-4F86-9BC7-5591A164A464@.microsoft.com...
>I would like to change the owner of several maintenance plans. I can't
>seem
> to find a way to that without recreating each plan while connected as the
> new
> owner. Is there a method similar to changing a job's owner via the job's
> properties or a database's by sp_changedbowner?
> Thanks|||Changing the owner of the job only works until the maintenance plan is
modified, then the jobs reverts back to the owner of the maintenance plan.
You might review this article:
http://sqlblogcasts.com/blogs/acalvett/archive/2007/12/30/the-job-whose-owner-kept-coming-back.aspx
"Russell Fields" wrote:
> Jim,
> Sorry, I don't know a neat way to change the maintenance plan owner. (In
> SQL Server 2000, you can go through the process to modify system tables and
> change the 'owner' column of msdb.dbo.sysdbmaintplans (although I don't
> recommend it) but in SQL Server 2005 that avenue has been closed.)
> You can, of course, change the job owner that runs the maintenance plan as
> you already described. Is there a reason that the maintenance plan owner
> needs to be changed? What side effects are you experiencing?
> RLF
> "Jim Early" <JimEarly@.discussions.microsoft.com> wrote in message
> news:DF28A436-C2D0-4F86-9BC7-5591A164A464@.microsoft.com...
> >I would like to change the owner of several maintenance plans. I can't
> >seem
> > to find a way to that without recreating each plan while connected as the
> > new
> > owner. Is there a method similar to changing a job's owner via the job's
> > properties or a database's by sp_changedbowner?
> >
> > Thanks
>
>|||Interesting. Thanks, RLF
"jed" <jed@.discussions.microsoft.com> wrote in message
news:96DC37A2-DD29-4EE2-A89D-F24A8DC41981@.microsoft.com...
> Changing the owner of the job only works until the maintenance plan is
> modified, then the jobs reverts back to the owner of the maintenance plan.
> You might review this article:
> http://sqlblogcasts.com/blogs/acalvett/archive/2007/12/30/the-job-whose-owner-kept-coming-back.aspx
> "Russell Fields" wrote:
>> Jim,
>> Sorry, I don't know a neat way to change the maintenance plan owner. (In
>> SQL Server 2000, you can go through the process to modify system tables
>> and
>> change the 'owner' column of msdb.dbo.sysdbmaintplans (although I don't
>> recommend it) but in SQL Server 2005 that avenue has been closed.)
>> You can, of course, change the job owner that runs the maintenance plan
>> as
>> you already described. Is there a reason that the maintenance plan owner
>> needs to be changed? What side effects are you experiencing?
>> RLF
>> "Jim Early" <JimEarly@.discussions.microsoft.com> wrote in message
>> news:DF28A436-C2D0-4F86-9BC7-5591A164A464@.microsoft.com...
>> >I would like to change the owner of several maintenance plans. I can't
>> >seem
>> > to find a way to that without recreating each plan while connected as
>> > the
>> > new
>> > owner. Is there a method similar to changing a job's owner via the
>> > job's
>> > properties or a database's by sp_changedbowner?
>> >
>> > Thanks
>>|||I encountered the same issue with the Maintenance Plan, when I made a change
to the MaintPlan, the Job changed to that of the owner of the MaintPlan.
According to some reading I have been doing, there really isnâ't a way to
change the Maintenance Plan Owner yet, I do understand why the MaintPlan
owner needs to be changed. In my case, the old DBA has been moved to another
section and no longer have rights to the SQL Server yet, when a change is
made to the MaintPlan, the job defaults to his name and the job fails. Also,
if the person is no longer an employee of the company yet, he created the
plans under his account, the MaintPlan defaults to his account or the account
he used when in SQL.
What I did to change the Owner of all Maintenance Plans in SQL 2005 was, if
you use windows authentication in SQL â'run asâ' the SQL account that is the
manager of SQL or connect to SQL using the SQL Account that manages your SQL.
From there, modify the Maintenance Plan, such as rename it, then rename it
back; the MaintPlan will assume the account you are using as the â'ownerâ'.
This may be tedious but it worksâ?¦
"Jim Early" wrote:
> I would like to change the owner of several maintenance plans. I can't seem
> to find a way to that without recreating each plan while connected as the new
> owner. Is there a method similar to changing a job's owner via the job's
> properties or a database's by sp_changedbowner?
> Thanks|||/*Here's how to change the owner of a maintenance plan to dbo in SQL Server
2005*/
--to find the name and owner of the maintenance plan
--select * from msdb.dbo.sysdtspackages90
--to find the sid you want to use for the new owner
--select * from sysusers
UPDATE
[msdb].[dbo].[sysdtspackages90]
SET
[ownersid] = 0x01
WHERE
[name] = 'MaintenancePlan'|||/*Here's how to change the owner of a maintenance plan to dbo in SQL Server
2005*/
--to find the name and owner of the maintenance plan
--select * from msdb.dbo.sysdtspackages90
--to find the sid you want to use for the new owner
--select * from sysusers
UPDATE
[msdb].[dbo].[sysdtspackages90]
SET
[ownersid] = 0x01
WHERE
[name] = 'MaintenancePlan'
"Jim Early" wrote:
> I would like to change the owner of several maintenance plans. I can't seem
> to find a way to that without recreating each plan while connected as the new
> owner. Is there a method similar to changing a job's owner via the job's
> properties or a database's by sp_changedbowner?
> Thanks
Change maintenance plan owner
Jim,
Sorry, I don't know a neat way to change the maintenance plan owner. (In
SQL Server 2000, you can go through the process to modify system tables and
change the 'owner' column of msdb.dbo.sysdbmaintplans (although I don't
recommend it) but in SQL Server 2005 that avenue has been closed.)
You can, of course, change the job owner that runs the maintenance plan as
you already described. Is there a reason that the maintenance plan owner
needs to be changed? What side effects are you experiencing?
RLF
"Jim Early" <JimEarly@.discussions.microsoft.com> wrote in message
news:DF28A436-C2D0-4F86-9BC7-5591A164A464@.microsoft.com...
>I would like to change the owner of several maintenance plans. I can't
>seem
> to find a way to that without recreating each plan while connected as the
> new
> owner. Is there a method similar to changing a job's owner via the job's
> properties or a database's by sp_changedbowner?
> Thanks
Changing the owner of the job only works until the maintenance plan is
modified, then the jobs reverts back to the owner of the maintenance plan.
You might review this article:
http://sqlblogcasts.com/blogs/acalvett/archive/2007/12/30/the-job-whose-owner-kept-coming-back.aspx
"Russell Fields" wrote:
> Jim,
> Sorry, I don't know a neat way to change the maintenance plan owner. (In
> SQL Server 2000, you can go through the process to modify system tables and
> change the 'owner' column of msdb.dbo.sysdbmaintplans (although I don't
> recommend it) but in SQL Server 2005 that avenue has been closed.)
> You can, of course, change the job owner that runs the maintenance plan as
> you already described. Is there a reason that the maintenance plan owner
> needs to be changed? What side effects are you experiencing?
> RLF
> "Jim Early" <JimEarly@.discussions.microsoft.com> wrote in message
> news:DF28A436-C2D0-4F86-9BC7-5591A164A464@.microsoft.com...
>
>
|||Interesting. Thanks, RLF
"jed" <jed@.discussions.microsoft.com> wrote in message
news:96DC37A2-DD29-4EE2-A89D-F24A8DC41981@.microsoft.com...[vbcol=seagreen]
> Changing the owner of the job only works until the maintenance plan is
> modified, then the jobs reverts back to the owner of the maintenance plan.
> You might review this article:
> http://sqlblogcasts.com/blogs/acalvett/archive/2007/12/30/the-job-whose-owner-kept-coming-back.aspx
> "Russell Fields" wrote:
Sorry, I don't know a neat way to change the maintenance plan owner. (In
SQL Server 2000, you can go through the process to modify system tables and
change the 'owner' column of msdb.dbo.sysdbmaintplans (although I don't
recommend it) but in SQL Server 2005 that avenue has been closed.)
You can, of course, change the job owner that runs the maintenance plan as
you already described. Is there a reason that the maintenance plan owner
needs to be changed? What side effects are you experiencing?
RLF
"Jim Early" <JimEarly@.discussions.microsoft.com> wrote in message
news:DF28A436-C2D0-4F86-9BC7-5591A164A464@.microsoft.com...
>I would like to change the owner of several maintenance plans. I can't
>seem
> to find a way to that without recreating each plan while connected as the
> new
> owner. Is there a method similar to changing a job's owner via the job's
> properties or a database's by sp_changedbowner?
> Thanks
Changing the owner of the job only works until the maintenance plan is
modified, then the jobs reverts back to the owner of the maintenance plan.
You might review this article:
http://sqlblogcasts.com/blogs/acalvett/archive/2007/12/30/the-job-whose-owner-kept-coming-back.aspx
"Russell Fields" wrote:
> Jim,
> Sorry, I don't know a neat way to change the maintenance plan owner. (In
> SQL Server 2000, you can go through the process to modify system tables and
> change the 'owner' column of msdb.dbo.sysdbmaintplans (although I don't
> recommend it) but in SQL Server 2005 that avenue has been closed.)
> You can, of course, change the job owner that runs the maintenance plan as
> you already described. Is there a reason that the maintenance plan owner
> needs to be changed? What side effects are you experiencing?
> RLF
> "Jim Early" <JimEarly@.discussions.microsoft.com> wrote in message
> news:DF28A436-C2D0-4F86-9BC7-5591A164A464@.microsoft.com...
>
>
|||Interesting. Thanks, RLF
"jed" <jed@.discussions.microsoft.com> wrote in message
news:96DC37A2-DD29-4EE2-A89D-F24A8DC41981@.microsoft.com...[vbcol=seagreen]
> Changing the owner of the job only works until the maintenance plan is
> modified, then the jobs reverts back to the owner of the maintenance plan.
> You might review this article:
> http://sqlblogcasts.com/blogs/acalvett/archive/2007/12/30/the-job-whose-owner-kept-coming-back.aspx
> "Russell Fields" wrote:
Change maintenance plan owner
I encountered the same issue with the Maintenance Plan, when I made a change
to the MaintPlan, the Job changed to that of the owner of the MaintPlan.
According to some reading I have been doing, there really isn’t a way to
change the Maintenance Plan Owner yet, I do understand why the MaintPlan
owner needs to be changed. In my case, the old DBA has been moved to another
section and no longer have rights to the SQL Server yet, when a change is
made to the MaintPlan, the job defaults to his name and the job fails. Also,
if the person is no longer an employee of the company yet, he created the
plans under his account, the MaintPlan defaults to his account or the account
he used when in SQL.
What I did to change the Owner of all Maintenance Plans in SQL 2005 was, if
you use windows authentication in SQL “run as” the SQL account that is the
manager of SQL or connect to SQL using the SQL Account that manages your SQL.
From there, modify the Maintenance Plan, such as rename it, then rename it
back; the MaintPlan will assume the account you are using as the ‘owner’.
This may be tedious but it works…
"Jim Early" wrote:
> I would like to change the owner of several maintenance plans. I can't seem
> to find a way to that without recreating each plan while connected as the new
> owner. Is there a method similar to changing a job's owner via the job's
> properties or a database's by sp_changedbowner?
> Thanks
/*Here's how to change the owner of a maintenance plan to dbo in SQL Server
2005*/
--to find the name and owner of the maintenance plan
--select * from msdb.dbo.sysdtspackages90
--to find the sid you want to use for the new owner
--select * from sysusers
UPDATE
[msdb].[dbo].[sysdtspackages90]
SET
[ownersid] = 0x01
WHERE
[name] = 'MaintenancePlan'
to the MaintPlan, the Job changed to that of the owner of the MaintPlan.
According to some reading I have been doing, there really isn’t a way to
change the Maintenance Plan Owner yet, I do understand why the MaintPlan
owner needs to be changed. In my case, the old DBA has been moved to another
section and no longer have rights to the SQL Server yet, when a change is
made to the MaintPlan, the job defaults to his name and the job fails. Also,
if the person is no longer an employee of the company yet, he created the
plans under his account, the MaintPlan defaults to his account or the account
he used when in SQL.
What I did to change the Owner of all Maintenance Plans in SQL 2005 was, if
you use windows authentication in SQL “run as” the SQL account that is the
manager of SQL or connect to SQL using the SQL Account that manages your SQL.
From there, modify the Maintenance Plan, such as rename it, then rename it
back; the MaintPlan will assume the account you are using as the ‘owner’.
This may be tedious but it works…
"Jim Early" wrote:
> I would like to change the owner of several maintenance plans. I can't seem
> to find a way to that without recreating each plan while connected as the new
> owner. Is there a method similar to changing a job's owner via the job's
> properties or a database's by sp_changedbowner?
> Thanks
/*Here's how to change the owner of a maintenance plan to dbo in SQL Server
2005*/
--to find the name and owner of the maintenance plan
--select * from msdb.dbo.sysdtspackages90
--to find the sid you want to use for the new owner
--select * from sysusers
UPDATE
[msdb].[dbo].[sysdtspackages90]
SET
[ownersid] = 0x01
WHERE
[name] = 'MaintenancePlan'
Change maintenance plan owner
I would like to change the owner of several maintenance plans. I can't seem
to find a way to that without recreating each plan while connected as the ne
w
owner. Is there a method similar to changing a job's owner via the job's
properties or a database's by sp_changedbowner?
ThanksJim,
Sorry, I don't know a neat way to change the maintenance plan owner. (In
SQL Server 2000, you can go through the process to modify system tables and
change the 'owner' column of msdb.dbo.sysdbmaintplans (although I don't
recommend it) but in SQL Server 2005 that avenue has been closed.)
You can, of course, change the job owner that runs the maintenance plan as
you already described. Is there a reason that the maintenance plan owner
needs to be changed? What side effects are you experiencing?
RLF
"Jim Early" <JimEarly@.discussions.microsoft.com> wrote in message
news:DF28A436-C2D0-4F86-9BC7-5591A164A464@.microsoft.com...
>I would like to change the owner of several maintenance plans. I can't
>seem
> to find a way to that without recreating each plan while connected as the
> new
> owner. Is there a method similar to changing a job's owner via the job's
> properties or a database's by sp_changedbowner?
> Thanks|||Changing the owner of the job only works until the maintenance plan is
modified, then the jobs reverts back to the owner of the maintenance plan.
You might review this article:
http://sqlblogcasts.com/blogs/acalv...oming-back.aspx
"Russell Fields" wrote:
> Jim,
> Sorry, I don't know a neat way to change the maintenance plan owner. (In
> SQL Server 2000, you can go through the process to modify system tables an
d
> change the 'owner' column of msdb.dbo.sysdbmaintplans (although I don't
> recommend it) but in SQL Server 2005 that avenue has been closed.)
> You can, of course, change the job owner that runs the maintenance plan as
> you already described. Is there a reason that the maintenance plan owner
> needs to be changed? What side effects are you experiencing?
> RLF
> "Jim Early" <JimEarly@.discussions.microsoft.com> wrote in message
> news:DF28A436-C2D0-4F86-9BC7-5591A164A464@.microsoft.com...
>
>|||Interesting. Thanks, RLF
"jed" <jed@.discussions.microsoft.com> wrote in message
news:96DC37A2-DD29-4EE2-A89D-F24A8DC41981@.microsoft.com...[vbcol=seagreen]
> Changing the owner of the job only works until the maintenance plan is
> modified, then the jobs reverts back to the owner of the maintenance plan.
> You might review this article:
> http://sqlblogcasts.com/blogs/acalv...oming-back.aspx
> "Russell Fields" wrote:
>
to find a way to that without recreating each plan while connected as the ne
w
owner. Is there a method similar to changing a job's owner via the job's
properties or a database's by sp_changedbowner?
ThanksJim,
Sorry, I don't know a neat way to change the maintenance plan owner. (In
SQL Server 2000, you can go through the process to modify system tables and
change the 'owner' column of msdb.dbo.sysdbmaintplans (although I don't
recommend it) but in SQL Server 2005 that avenue has been closed.)
You can, of course, change the job owner that runs the maintenance plan as
you already described. Is there a reason that the maintenance plan owner
needs to be changed? What side effects are you experiencing?
RLF
"Jim Early" <JimEarly@.discussions.microsoft.com> wrote in message
news:DF28A436-C2D0-4F86-9BC7-5591A164A464@.microsoft.com...
>I would like to change the owner of several maintenance plans. I can't
>seem
> to find a way to that without recreating each plan while connected as the
> new
> owner. Is there a method similar to changing a job's owner via the job's
> properties or a database's by sp_changedbowner?
> Thanks|||Changing the owner of the job only works until the maintenance plan is
modified, then the jobs reverts back to the owner of the maintenance plan.
You might review this article:
http://sqlblogcasts.com/blogs/acalv...oming-back.aspx
"Russell Fields" wrote:
> Jim,
> Sorry, I don't know a neat way to change the maintenance plan owner. (In
> SQL Server 2000, you can go through the process to modify system tables an
d
> change the 'owner' column of msdb.dbo.sysdbmaintplans (although I don't
> recommend it) but in SQL Server 2005 that avenue has been closed.)
> You can, of course, change the job owner that runs the maintenance plan as
> you already described. Is there a reason that the maintenance plan owner
> needs to be changed? What side effects are you experiencing?
> RLF
> "Jim Early" <JimEarly@.discussions.microsoft.com> wrote in message
> news:DF28A436-C2D0-4F86-9BC7-5591A164A464@.microsoft.com...
>
>|||Interesting. Thanks, RLF
"jed" <jed@.discussions.microsoft.com> wrote in message
news:96DC37A2-DD29-4EE2-A89D-F24A8DC41981@.microsoft.com...[vbcol=seagreen]
> Changing the owner of the job only works until the maintenance plan is
> modified, then the jobs reverts back to the owner of the maintenance plan.
> You might review this article:
> http://sqlblogcasts.com/blogs/acalv...oming-back.aspx
> "Russell Fields" wrote:
>
Subscribe to:
Posts (Atom)