change MSDB owner: behavior

Showing posts with label behavior. Show all posts

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
>

Change to behavior in SQL 2005 SP2?

Sunday, February 12, 2012

Change of Behavior in Pipeline

Dear Forum Members,

As part of the pipeline work to improve performance and stability, we are considering a change in how buffers are managed, which will affect custom transforms. Michael Entin explained what is about to change in the link below:

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

My questions are :

What is the likelihood of your scenarios broken by this?

How expensive is it to change your custom component code as Michael suggests in his blog?

Thanks,

Deniz Erkan - msft

To summarize the impact, can I assume that we're only talking about SQL Server 2008, with which custom transformations for SQL 2005 would need to be recoded anyway so as to take advantage of the new interfaces (e.g. IDTSxxxx100), for example?

In other words, it shouldn't be a huge issue as SQL Server 2008 isn't even released, correct? And custom component development at this juncture might be a bit premature?

We're not changing the behavior in SQL Server 2005, are we?|||I have no problem with this because I saw that blog entry and fixed my code. |||

>We're not changing the behavior in SQL Server 2005, are we?

The SQL 2005 behavior will not be changed.

The concern is scenario when someone takes a component developed for SQL 2005, mechanically renames the interfaces, recompiles it and and uses it in SQL 2008. If the component used incorrect pattern, the new version will be producing incorrect results in SQL 2008.

|||

Phil,

Yes, your assumptions are correct. This is only for 2008, and custom components developed for 2005 will have to be re-compiled at a minimum to work against the new interfaces.

However, what otherwise would be a simple re-compile job, now would be an additional check on the semantics of the custom component to make sure the "end of buffer logic" is done properly as per Michael's post.

oh, and I didn't see Michael's post prior to this, apologies for repetition.

|||

I don't see a major issue with this. As part of an ETL process, the record counts should be reconciled anyway, so this should be easy to catch.

|||

Transform component authors need to change behavior together for 2008, as not everyone has access to the source code of all the non stock components they use. To me, this issue is more coordination then rocket science...

As jwelch mentioned, I don't think the problem would be all that difficult to detect, since rowcounts should be part of an etl process, but then there's the fix and recompile part...

For example, we don't have source code for some non-stock components, but Reflector shows they exhibit the incorrect behavior. So while I've recompiled owned source code, I just hope everybody gets the message, or is willing to part with source.

|||

I rememeber this confusing me at the time I started writing components. I got it into my head that calling buffer.NextRow() when there weren't any caused an error so I put the EndOfRowset check first.

Not too difficult to change in my components.

Will SQL2005 compiled components work in a SQL2008 pipeline, if not then everyone has to recompile there code anyway.

|||

SimonS_ wrote:
Will SQL2005 compiled components work in a SQL2008 pipeline, if not then everyone has to recompile there [sic] code anyway.

No. You have to edit and recompile to use the new interfaces.

http://blogs.msdn.com/mattm/archive/2007/06/05/katmai-custom-components-and-upgrade.aspx|||

There's no big issue here that affects me. I have one question though - this will ge changed in the docs right?

And, have the SSIS2005 docs been updated yet as per Michael's post? I know Michael said that this was work-in-progress.

-Jamie

Change of Behavior in Pipeline

Dear Forum Members,

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

My questions are :

What is the likelihood of your scenarios broken by this?

How expensive is it to change your custom component code as Michael suggests in his blog?

Thanks,

Deniz Erkan - msft

In other words, it shouldn't be a huge issue as SQL Server 2008 isn't even released, correct? And custom component development at this juncture might be a bit premature?

We're not changing the behavior in SQL Server 2005, are we?|||I have no problem with this because I saw that blog entry and fixed my code. |||

>We're not changing the behavior in SQL Server 2005, are we?

The SQL 2005 behavior will not be changed.

|||

Phil,

Yes, your assumptions are correct. This is only for 2008, and custom components developed for 2005 will have to be re-compiled at a minimum to work against the new interfaces.

oh, and I didn't see Michael's post prior to this, apologies for repetition.

|||

I don't see a major issue with this. As part of an ETL process, the record counts should be reconciled anyway, so this should be easy to catch.

|||

As jwelch mentioned, I don't think the problem would be all that difficult to detect, since rowcounts should be part of an etl process, but then there's the fix and recompile part...

|||

Not too difficult to change in my components.

Will SQL2005 compiled components work in a SQL2008 pipeline, if not then everyone has to recompile there code anyway.

|||

SimonS_ wrote:
Will SQL2005 compiled components work in a SQL2008 pipeline, if not then everyone has to recompile there [sic] code anyway.

No. You have to edit and recompile to use the new interfaces.

http://blogs.msdn.com/mattm/archive/2007/06/05/katmai-custom-components-and-upgrade.aspx|||

There's no big issue here that affects me. I have one question though - this will ge changed in the docs right?

And, have the SSIS2005 docs been updated yet as per Michael's post? I know Michael said that this was work-in-progress.

-Jamie

Change of Behavior in Pipeline

Dear Forum Members,

http://blogs.msdn.com/michen/archive/2007/08/31/Buffer.EndOfRowset.aspx

My questions are :

What is the likelihood of your scenarios broken by this?

How expensive is it to change your custom component code as Michael suggests in his blog?

Thanks,

Deniz Erkan - msft

In other words, it shouldn't be a huge issue as SQL Server 2008 isn't even released, correct? And custom component development at this juncture might be a bit premature?

We're not changing the behavior in SQL Server 2005, are we?|||I have no problem with this because I saw that blog entry and fixed my code. |||

>We're not changing the behavior in SQL Server 2005, are we?

The SQL 2005 behavior will not be changed.

|||

Phil,

Yes, your assumptions are correct. This is only for 2008, and custom components developed for 2005 will have to be re-compiled at a minimum to work against the new interfaces.

oh, and I didn't see Michael's post prior to this, apologies for repetition.

|||

I don't see a major issue with this. As part of an ETL process, the record counts should be reconciled anyway, so this should be easy to catch.

|||

As jwelch mentioned, I don't think the problem would be all that difficult to detect, since rowcounts should be part of an etl process, but then there's the fix and recompile part...

|||

Not too difficult to change in my components.

Will SQL2005 compiled components work in a SQL2008 pipeline, if not then everyone has to recompile there code anyway.

|||

SimonS_ wrote:
Will SQL2005 compiled components work in a SQL2008 pipeline, if not then everyone has to recompile there [sic] code anyway.

No. You have to edit and recompile to use the new interfaces.

http://blogs.msdn.com/mattm/archive/2007/06/05/katmai-custom-components-and-upgrade.aspx|||

There's no big issue here that affects me. I have one question though - this will ge changed in the docs right?

And, have the SSIS2005 docs been updated yet as per Michael's post? I know Michael said that this was work-in-progress.

-Jamie

Thursday, March 8, 2012

Change to behavior in SQL 2005 SP2?

Change to behavior in SQL 2005 SP2?

Change to behavior in SQL 2005 SP2?

Sunday, February 12, 2012

Change of Behavior in Pipeline

Change of Behavior in Pipeline

Change of Behavior in Pipeline

change MSDB owner

Blog Archive

About Me