Differentials on Master

Just in case we overlook backing up Master after executing statements that modify it, we also have once a week full backups accompanied by a nightly differential using the NOINIT clause to append to the same backup device the full backups of Master are be
ing saved.
1. Should only full backups be made of Master, or are differentials in between full backups allowed?
Message posted via http://www.sqlmonster.com
The systems dbs are usually way to small to do anything other than a FULL
backup. It can only take a few seconds to do a full backup on the Master
DB.
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be4f91128ce74dc3b75ac0d3674ed8ea@.SQLMonster.c om...
> Just in case we overlook backing up Master after executing statements that
> modify it, we also have once a week full backups accompanied by a nightly
> differential using the NOINIT clause to append to the same backup device
> the full backups of Master are being saved.
> 1. Should only full backups be made of Master, or are differentials in
> between full backups allowed?
> --
> Message posted via http://www.sqlmonster.com
|||My problem is disk space, and for redundancy appending one backup set on top of another, so a full backup, on top of a full backup to the same backup device using NOINIT, chews up needed disk space.
1. Should only full backups be made of Master, or are differentials in between full backups allowed?
2. From what I can tell (implied from your first response) that performing differentials on Master is allowed?
3. Restoring Master using differentials would not be any more dangerous that restoring any other database using differentials, or is this a false statement?
Message posted via http://www.sqlmonster.com
|||I suppose Diff's on Master is allowed. If you are that tight on disk space
you are in trouble and that should be remedied. Master DB is only a few MB
in size and there are lots of other things that can each up disk space
faster than a Master Full backup. Why are you appending them to the same
device then? If you create a new file for each backup you can delete the
old ones before you add the new one and never use more disk space than a
full cycle (how ever many days you want to keeps worth).
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:7151d81a3055437b89361c204cb6fce1@.SQLMonster.c om...
> My problem is disk space, and for redundancy appending one backup set on
> top of another, so a full backup, on top of a full backup to the same
> backup device using NOINIT, chews up needed disk space.
> 1. Should only full backups be made of Master, or are differentials in
> between full backups allowed?
> 2. From what I can tell (implied from your first response) that performing
> differentials on Master is allowed?
> 3. Restoring Master using differentials would not be any more dangerous
> that restoring any other database using differentials, or is this a false
> statement?
> --
> Message posted via http://www.sqlmonster.com

Differentials on Master

Just in case we overlook backing up Master after executing statements that m
odify it, we also have once a week full backups accompanied by a nightly dif
ferential using the NOINIT clause to append to the same backup device the fu
ll backups of Master are be
ing saved.
1. Should only full backups be made of Master, or are differentials in betwe
en full backups allowed?
Message posted via http://www.droptable.comThe systems dbs are usually way to small to do anything other than a FULL
backup. It can only take a few seconds to do a full backup on the Master
DB.
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:be4f91128ce74dc3b75ac0d3674ed8ea@.SQ
droptable.com...
> Just in case we overlook backing up Master after executing statements that
> modify it, we also have once a week full backups accompanied by a nightly
> differential using the NOINIT clause to append to the same backup device
> the full backups of Master are being saved.
> 1. Should only full backups be made of Master, or are differentials in
> between full backups allowed?
> --
> Message posted via http://www.droptable.com|||My problem is disk space, and for redundancy appending one backup set on top
of another, so a full backup, on top of a full backup to the same backup de
vice using NOINIT, chews up needed disk space.
1. Should only full backups be made of Master, or are differentials in betwe
en full backups allowed?
2. From what I can tell (implied from your first response) that performing d
ifferentials on Master is allowed?
3. Restoring Master using differentials would not be any more dangerous that
restoring any other database using differentials, or is this a false statem
ent?
Message posted via http://www.droptable.com|||I suppose Diff's on Master is allowed. If you are that tight on disk space
you are in trouble and that should be remedied. Master DB is only a few MB
in size and there are lots of other things that can each up disk space
faster than a Master Full backup. Why are you appending them to the same
device then? If you create a new file for each backup you can delete the
old ones before you add the new one and never use more disk space than a
full cycle (how ever many days you want to keeps worth).
Andrew J. Kelly SQL MVP
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:7151d81a3055437b89361c204cb6fce1@.SQ
droptable.com...
> My problem is disk space, and for redundancy appending one backup set on
> top of another, so a full backup, on top of a full backup to the same
> backup device using NOINIT, chews up needed disk space.
> 1. Should only full backups be made of Master, or are differentials in
> between full backups allowed?
> 2. From what I can tell (implied from your first response) that performing
> differentials on Master is allowed?
> 3. Restoring Master using differentials would not be any more dangerous
> that restoring any other database using differentials, or is this a false
> statement?
> --
> Message posted via http://www.droptable.com

Differentials on Master

Just in case we overlook backing up Master after executing statements that modify it, we also have once a week full backups accompanied by a nightly differential using the NOINIT clause to append to the same backup device the full backups of Master are being saved.
1. Should only full backups be made of Master, or are differentials in between full backups allowed?
--
Message posted via http://www.sqlmonster.comThe systems dbs are usually way to small to do anything other than a FULL
backup. It can only take a few seconds to do a full backup on the Master
DB.
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:be4f91128ce74dc3b75ac0d3674ed8ea@.SQLMonster.com...
> Just in case we overlook backing up Master after executing statements that
> modify it, we also have once a week full backups accompanied by a nightly
> differential using the NOINIT clause to append to the same backup device
> the full backups of Master are being saved.
> 1. Should only full backups be made of Master, or are differentials in
> between full backups allowed?
> --
> Message posted via http://www.sqlmonster.com|||My problem is disk space, and for redundancy appending one backup set on top of another, so a full backup, on top of a full backup to the same backup device using NOINIT, chews up needed disk space.
1. Should only full backups be made of Master, or are differentials in between full backups allowed?
2. From what I can tell (implied from your first response) that performing differentials on Master is allowed?
3. Restoring Master using differentials would not be any more dangerous that restoring any other database using differentials, or is this a false statement?
--
Message posted via http://www.sqlmonster.com|||I suppose Diff's on Master is allowed. If you are that tight on disk space
you are in trouble and that should be remedied. Master DB is only a few MB
in size and there are lots of other things that can each up disk space
faster than a Master Full backup. Why are you appending them to the same
device then? If you create a new file for each backup you can delete the
old ones before you add the new one and never use more disk space than a
full cycle (how ever many days you want to keeps worth).
--
Andrew J. Kelly SQL MVP
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:7151d81a3055437b89361c204cb6fce1@.SQLMonster.com...
> My problem is disk space, and for redundancy appending one backup set on
> top of another, so a full backup, on top of a full backup to the same
> backup device using NOINIT, chews up needed disk space.
> 1. Should only full backups be made of Master, or are differentials in
> between full backups allowed?
> 2. From what I can tell (implied from your first response) that performing
> differentials on Master is allowed?
> 3. Restoring Master using differentials would not be any more dangerous
> that restoring any other database using differentials, or is this a false
> statement?
> --
> Message posted via http://www.sqlmonster.com

Different results when executing from .NET component compare to executing from SQL Managem

Hi all,

I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.
This started to really annoying me, any thoughts or solution?
Thanks very much guys

I'm interested in some more details: what's the meaning of different results? Returning different data (rows)? Or same data (rows) in different orders? Can you post the stored proceudre if it does not contain too many statements?

Since recomplie the stored procedure can solve the issue, it seems SQL optimizer chooses different execution plans, thus may lead to different results. One work around is to call the stored procedure with recomplie:

EXECUTEyourSPName WITH RECOMPILE

However this is not a so good solution, as RECOMPLIE at each execution will impact SQL performance.

|||

The result that come back seems to be from the old version of the stored procedure, or could be it's joining differently.
I am suspecting that .NET SQL provider has a seperate query plan cache. The actual select statement is like this, I removed few lines in the where clause and column selections.
SELECT
main.*,fa.*
FROM [NetFare] main
INNER JOIN dbo.Agent fa
ON main.FareId = fa.FareId
LEFT OUTER JOIN travel.dbo.currencies c
ON c.Code = main.FareCurrency
Where

AND (PriceReturnType = @.ReturnType)
And main.PortSetID in (Select Distinct PortSetID
From FarePortSetMember Where PortCode = @.DestinationPort Or PortCode = @.DestinationCity)
And main.OriginPortSetId in (Select Distinct FarePortSetId
From FarePortSetMember Where PortCode = @.OriginPort Or PortCode = @.OriginCity)
AND ((DateFrom <= @.EarliestDepartureDate) AND (DateTo >= @.EarliestDepartureDate))
AND (DATEDIFF(day, TicketDateFrom,GetDate()) >= 0 AND DATEDIFF(day, TicketDateTo, getdate()) <=0)
And status in ('Active','Updating')

Option (KEEPFIXED PLAN)

I just added the OPTION (KEEPFIXED PLAN) and have been monitoring to see if the problem happens again.
So there is no fancy stuff in the query, but it seems to me that the .NET SQL provider are using different query plan.

Could MVP dudes verify this for us, please?

I am using SQL 2000 by the way and .NET 1.1 component called from ASP.NET 2.0