Frustrating SubQuery Won't Total

Can someone help me determine why my subquery below is not totaling. Without
having to dig to deeply into the logic and data, I am hoping that seeing the
working/non working versions together might reveal something obvious. I have
removed the other selected columns from the main query.
I have tried using distinct and grouping in the subquery and using grouping
in the outer query to no avail.
Version 1 'unexpected' result with syntax is as it would be normally in the
query
TaxablePremium
--
1000.00
2000.00
Version 2 expected result with the FKs that would be passed from the outer
query assigned
TaxablePremium
--
3000.00
SELECT distinct
/* SubQuery version 1 - syntax is as it would be normally in the query*/
(SELECT SUM(QuotePremium.Premium)
FROM QuotePremium
INNER JOIN QuoteLine ON QuotePremium.QuoteLineID = QuoteLine.ID
INNER JOIN QuoteTaxPremium ON QuotePremium.ID =
QuoteTaxPremium.QuotePremiumID
WHERE (QuoteLine.InvoiceID = ILI1.InvoiceId)
AND (QuoteTaxPremium.TaxLineID in(InvoiceLineItemTax.taxlobId))
) as TaxablePremium
/* SubQuery version 2- FKs that would be passed from the outer query are
assigned */
(SELECT SUM(QuotePremium.Premium)
FROM QuotePremium
INNER JOIN QuoteLine ON QuotePremium.QuoteLineID = QuoteLine.ID
INNER JOIN QuoteTaxPremium ON QuotePremium.ID =
QuoteTaxPremium.QuotePremiumID
WHERE (QuoteLine.InvoiceID = 5947) AND (QuoteTaxPremium.TaxLineID IN
(4298, 4353)))
AS TaxablePremium
FROM InvoiceLineItem ILI1
INNER JOIN InvoiceLineItemTax ON ILI1.ID =
InvoiceLineItemTax.InvoiceLineItemID
INNER JOIN TaxLOB ON InvoiceLineItemTax.TaxLOBID = TaxLOB.ID
INNER JOIN Tax ON TaxLOB.TaxID = Tax.ID
INNER JOIN Invoice ON ILI1.InvoiceID = Invoice.ID
INNER JOIN Office Office1 ON Invoice.InsuredID = Office1.ID
INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
INNER JOIN Office Office2 ON Tax.RegulatoryAuthorityID = Office2.ID
WHERE (InvoiceLineItemTax.FilingPartyType = 'A')
AND (Invoice.IsFutureTransaction = 0) AND (Invoice.APTaxBalance <> 0)
AND (ILI1.IsContraDistribution = 0) AND (ILI1.LineItemAccountingType = 2)
AND (ILI1.Balance <> 0) AND (NOT (Invoice.AMSPostDate IS NULL))
AND (Office2.ID = @.ReglAuthId) AND (Tax.ID = @.TaxId)
AND (Invoice.InvoiceNumber =@.InvNbr1)Mike
If you show us a table structute with sample data we'd probably suggest
something.
In the first query you JOIN InvoiceID to ILI1.InvoiceId and here is I think
a problem. Try to look at your tables more carefully to see what is going
on when you're trying to JOIN them and to assign values.
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:OZtxqQY7FHA.3752@.tk2msftngp13.phx.gbl...
> Can someone help me determine why my subquery below is not totaling.
> Without having to dig to deeply into the logic and data, I am hoping that
> seeing the working/non working versions together might reveal something
> obvious. I have removed the other selected columns from the main query.
> I have tried using distinct and grouping in the subquery and using
> grouping in the outer query to no avail.
> Version 1 'unexpected' result with syntax is as it would be normally in
> the query
> TaxablePremium
> --
> 1000.00
> 2000.00
> Version 2 expected result with the FKs that would be passed from the outer
> query assigned
> TaxablePremium
> --
> 3000.00
>
> SELECT distinct
> /* SubQuery version 1 - syntax is as it would be normally in the query*/
> (SELECT SUM(QuotePremium.Premium)
> FROM QuotePremium
> INNER JOIN QuoteLine ON QuotePremium.QuoteLineID = QuoteLine.ID
> INNER JOIN QuoteTaxPremium ON QuotePremium.ID =
> QuoteTaxPremium.QuotePremiumID
> WHERE (QuoteLine.InvoiceID = ILI1.InvoiceId)
> AND (QuoteTaxPremium.TaxLineID in(InvoiceLineItemTax.taxlobId))
> ) as TaxablePremium
> /* SubQuery version 2- FKs that would be passed from the outer query are
> assigned */
> (SELECT SUM(QuotePremium.Premium)
> FROM QuotePremium
> INNER JOIN QuoteLine ON QuotePremium.QuoteLineID = QuoteLine.ID
> INNER JOIN QuoteTaxPremium ON QuotePremium.ID =
> QuoteTaxPremium.QuotePremiumID
> WHERE (QuoteLine.InvoiceID = 5947) AND (QuoteTaxPremium.TaxLineID IN
> (4298, 4353)))
> AS TaxablePremium
> FROM InvoiceLineItem ILI1
> INNER JOIN InvoiceLineItemTax ON ILI1.ID =
> InvoiceLineItemTax.InvoiceLineItemID
> INNER JOIN TaxLOB ON InvoiceLineItemTax.TaxLOBID = TaxLOB.ID
> INNER JOIN Tax ON TaxLOB.TaxID = Tax.ID
> INNER JOIN Invoice ON ILI1.InvoiceID = Invoice.ID
> INNER JOIN Office Office1 ON Invoice.InsuredID = Office1.ID
> INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
> INNER JOIN Office Office2 ON Tax.RegulatoryAuthorityID = Office2.ID
> WHERE (InvoiceLineItemTax.FilingPartyType = 'A')
> AND (Invoice.IsFutureTransaction = 0) AND (Invoice.APTaxBalance <> 0)
> AND (ILI1.IsContraDistribution = 0) AND (ILI1.LineItemAccountingType = 2)
> AND (ILI1.Balance <> 0) AND (NOT (Invoice.AMSPostDate IS NULL))
> AND (Office2.ID = @.ReglAuthId) AND (Tax.ID = @.TaxId)
> AND (Invoice.InvoiceNumber =@.InvNbr1)
>|||"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:OZtxqQY7FHA.3752@.tk2msftngp13.phx.gbl...
> Can someone help me determine why my subquery below is not
totaling. Without
> having to dig to deeply into the logic and data, I am hoping that
seeing the
> working/non working versions together might reveal something
obvious. I have
> removed the other selected columns from the main query.
> I have tried using distinct and grouping in the subquery and using
grouping
> in the outer query to no avail.
> Version 1 'unexpected' result with syntax is as it would be
normally in the
> query
> TaxablePremium
> --
> 1000.00
> 2000.00
> Version 2 expected result with the FKs that would be passed from
the outer
> query assigned
> TaxablePremium
> --
> 3000.00
>
> SELECT distinct
> /* SubQuery version 1 - syntax is as it would be normally in the
query*/
> (SELECT SUM(QuotePremium.Premium)
> FROM QuotePremium
> INNER JOIN QuoteLine ON QuotePremium.QuoteLineID = QuoteLine.ID
> INNER JOIN QuoteTaxPremium ON QuotePremium.ID =
> QuoteTaxPremium.QuotePremiumID
> WHERE (QuoteLine.InvoiceID = ILI1.InvoiceId)
> AND (QuoteTaxPremium.TaxLineID
in(InvoiceLineItemTax.taxlobId))
> ) as TaxablePremium
> /* SubQuery version 2- FKs that would be passed from the outer
query are
> assigned */
> (SELECT SUM(QuotePremium.Premium)
> FROM QuotePremium
> INNER JOIN QuoteLine ON QuotePremium.QuoteLineID = QuoteLine.ID
> INNER JOIN QuoteTaxPremium ON QuotePremium.ID =
> QuoteTaxPremium.QuotePremiumID
> WHERE (QuoteLine.InvoiceID = 5947) AND
(QuoteTaxPremium.TaxLineID IN
> (4298, 4353)))
> AS TaxablePremium
> FROM InvoiceLineItem ILI1
> INNER JOIN InvoiceLineItemTax ON ILI1.ID =
> InvoiceLineItemTax.InvoiceLineItemID
> INNER JOIN TaxLOB ON InvoiceLineItemTax.TaxLOBID = TaxLOB.ID
> INNER JOIN Tax ON TaxLOB.TaxID = Tax.ID
> INNER JOIN Invoice ON ILI1.InvoiceID = Invoice.ID
> INNER JOIN Office Office1 ON Invoice.InsuredID = Office1.ID
> INNER JOIN InvoiceType ON Invoice.InvoiceTypeID = InvoiceType.ID
> INNER JOIN Office Office2 ON Tax.RegulatoryAuthorityID =
Office2.ID
> WHERE (InvoiceLineItemTax.FilingPartyType = 'A')
> AND (Invoice.IsFutureTransaction = 0) AND (Invoice.APTaxBalance <>
0)
> AND (ILI1.IsContraDistribution = 0) AND
(ILI1.LineItemAccountingType = 2)
> AND (ILI1.Balance <> 0) AND (NOT (Invoice.AMSPostDate IS NULL))
> AND (Office2.ID = @.ReglAuthId) AND (Tax.ID = @.TaxId)
> AND (Invoice.InvoiceNumber =@.InvNbr1)
>
Mike Harbinger,
Without a fully reproducible script to test with
(http://www.aspfaq.com/etiquette.asp?id=5006), it is difficult to
say for sure.
However, I do spot something interesting.
Query 1:

>AND (QuoteTaxPremium.TaxLineID in(InvoiceLineItemTax.taxlobId))
This seems a bit unusual at first glance. Why specify IN (the
shortcut for <this> OR <that> OR <the other> ) when there is but one
column named? I would have thought it should just be:
AND (QuoteTaxPremium.TaxLineID = InvoiceLineItemTax.taxlobId)
And then I see:
Query 2:

> WHERE (QuoteLine.InvoiceID = 5947)
> AND (QuoteTaxPremium.TaxLineID IN (4298, 4353)))
> AS TaxablePremium
This is supposed to be the equivalent of Query 1, only with literals
assigned. Except that two literals are assigned in the IN() list.
In Query 1, only 1 value will be present.
This makes Query 1 slightly different from what is implied in the
construction of the IN() in Query 2.
Is it enough of a difference to be causing the problem in question?
My apologies, but I can't say for sure.
Sincererely,
Chris O.|||Uri
Thank you for your prompt reply.
Below is the data from my earlier sample post.
I have omitted only those columns that are not
material to this portion of the result set.
What we have is an invoice that contains an
insurance premium amount and a tax amount. I am
trying to show a specific premium which is subject
to a specific tax.
That would be indicated by an invoice that has
a tax amount (ILI1) and a premium (QuotePremium)
joined on TaxLOB and Tax.
Path to tax via premium amount:
Invoice\QuoteLine\QuotePremium\QuoteTaxP
remium\TaxLOB\Tax
Path to tax via tax amount:
Invoice\InvoiceLineItem (as ILI1)\InvicelineItemTax\TaxLOB\Tax
Table- QuoteLine
ID InvoiceID
20370 5947
20369 5947
Table- QuotePremium
ID QuoteLineID Premium
19865 20370 1000
19864 20369 2000
Table- QuoteTaxPremium
ID QuotePremiumID TaxLineID
181069 19865 4298
181070 19864 4353
InvoiceLineItemTax.InvoiceLineItemID
50405
50405
InvoiceLineItemTax.TaxLOBID
4298
4353
Table- TaxLOB
ID TaxID
4298 54
4353 54
Tax.ID
54
54
Invoice.ID
5947
5947
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23QmpQJZ7FHA.3200@.TK2MSFTNGP11.phx.gbl...
> Mike
> If you show us a table structute with sample data we'd probably suggest
> something.
>
> In the first query you JOIN InvoiceID to ILI1.InvoiceId and here is I
> think a problem. Try to look at your tables more carefully to see what is
> going on when you're trying to JOIN them and to assign values.
>
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:OZtxqQY7FHA.3752@.tk2msftngp13.phx.gbl...
>|||Chris
Actually the IN was recently added in debugging and didn't affect anything.
It originally was '='
There are 2 records in the test data because there are 2 when the query is
run resulting in the
2 premium amounts (1,000 and 2,000). What I don't understand is why the SUM
is not totalling
no matter how many records there are. Perhaps I don't fully understand how
the function works.
My apologies for not following protocol and thank you for passing along that
link. I was reluctant to bother you with the entire script and was hoping I
had missed something fairly obvious. In order for me to replicate the data
set I would have to dump many tables and force you to to deal with FK
constraints. I was not aware you would be willing to deal with all that. I
am reviewing the link to see if I can properly ge the data togehter.
Many thanks for your time on this!!
Mike
"Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in message
news:372dnfkES4z6ih3enZ2dnUVZ_s6dnZ2d@.co
mcast.com...
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:OZtxqQY7FHA.3752@.tk2msftngp13.phx.gbl...
> totaling. Without
> seeing the
> obvious. I have
> grouping
> normally in the
> the outer
> query*/
> in(InvoiceLineItemTax.taxlobId))
> query are
> (QuoteTaxPremium.TaxLineID IN
> Office2.ID
> 0)
> (ILI1.LineItemAccountingType = 2)
> Mike Harbinger,
> Without a fully reproducible script to test with
> (http://www.aspfaq.com/etiquette.asp?id=5006), it is difficult to
> say for sure.
> However, I do spot something interesting.
> Query 1:
>
> This seems a bit unusual at first glance. Why specify IN (the
> shortcut for <this> OR <that> OR <the other> ) when there is but one
> column named? I would have thought it should just be:
> AND (QuoteTaxPremium.TaxLineID = InvoiceLineItemTax.taxlobId)
>
> And then I see:
> Query 2:
>
> This is supposed to be the equivalent of Query 1, only with literals
> assigned. Except that two literals are assigned in the IN() list.
> In Query 1, only 1 value will be present.
> This makes Query 1 slightly different from what is implied in the
> construction of the IN() in Query 2.
> Is it enough of a difference to be causing the problem in question?
> My apologies, but I can't say for sure.
>
> Sincererely,
> Chris O.
>|||"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:%23edXata7FHA.2012@.TK2MSFTNGP14.phx.gbl...
>
> "Chris2" <rainofsteel.NOTVALID@.GETRIDOF.luminousrain.com> wrote in
message
> news:372dnfkES4z6ih3enZ2dnUVZ_s6dnZ2d@.co
mcast.com...
<snip>
> Chris
> Actually the IN was recently added in debugging and didn't affect
anything.
> It originally was '='
> There are 2 records in the test data because there are 2 when the
query is
> run resulting in the
> 2 premium amounts (1,000 and 2,000). What I don't understand is
why the SUM
> is not totalling
> no matter how many records there are. Perhaps I don't fully
understand how
> the function works.
> My apologies for not following protocol and thank you for passing
along that
> link. I was reluctant to bother you with the entire script and was
hoping I
> had missed something fairly obvious. In order for me to replicate
the data
> set I would have to dump many tables and force you to to deal with
FK
> constraints. I was not aware you would be willing to deal with all
that. I
> am reviewing the link to see if I can properly ge the data
togehter.
> Many thanks for your time on this!!
> Mike
Mike,
Please don't hestitate to force me to deal with the necessities of
your database! :)
As for dumping several tables, we only need a few key rows pere
table, with the data embedded in INSERT statements.
With a script along the lines of . . .
CREATE TABLE . . .
CREATE TABLE . . .
INSERT . . .
INSERT . . .
EXPECTED RESULTS . . .
QUERY/STORED PROC/ETC. . . .
ACTUAL RESULTS . . .
DROP TABLE . . .
DROP TABLE . . .
Anyone here can copy and paste a script like the above into QA or
Express Manager (or even OSQL!), and away we go with trying to fix
your problem instead of trying to figure out the answers for any of
a number of intermediary questions that are necessary to reaching a
solution.
Sincerely,
Chris O.

Free space of my filegroup

Hi:
How can i get the Total and Free space of my filegroups, do you have some script for that?
Thnaks :eek:What DBMS are you talking about?|||Hi:

SQL Server 2000, thank's for your answer.|||You posted in wrong forum then. I will move you...|||Thank you :o

Free and Used Allocated Space with Shrinkfile

SQL 7.0 NT 2000 server
Before doing anything:
Used: Free: Total
20GB 20GB 40GB
After
dbcc shrinkfile(1,notruncate)
followed by
dbcc shrinkfile(1,truncateonly)
the view from EM is:
Used: Free: Total
20GB 1.6MB 21GB
Within 24 hours
Used: Free: Total
20GB 12GB 32GB
The db and trans log are set to grow automatically by 10%
so how can the MDF file suddenly go to 32GB? Shouldn't
SQL expand the 1.6MB by 10% when needed, and then 10%
after that, etc?
There have been no deletions in the DB only insertions.
Thanks for your help.
DonThe growth is most likely due to a reindexing job (probably a maintenance
plan) at night. The growth is 10% of the entire file and not the 1.6MB
free. So the first growth would be 2GB and it just gets larger from there.
When you have a db over a few GB it is a good idea to change the growth
increment to a fixed amount and not a percentage so it always grows in a
controllable fashion. But never shrink an active database down to anywhere
near the size of the data. The db needs lots of free space to work
properly.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:246501c50239$11fd94f0$a601280a@.phx.gbl...
> SQL 7.0 NT 2000 server
> Before doing anything:
> Used: Free: Total
> 20GB 20GB 40GB
> After
> dbcc shrinkfile(1,notruncate)
> followed by
> dbcc shrinkfile(1,truncateonly)
> the view from EM is:
> Used: Free: Total
> 20GB 1.6MB 21GB
> Within 24 hours
> Used: Free: Total
> 20GB 12GB 32GB
> The db and trans log are set to grow automatically by 10%
> so how can the MDF file suddenly go to 32GB? Shouldn't
> SQL expand the 1.6MB by 10% when needed, and then 10%
> after that, etc?
> There have been no deletions in the DB only insertions.
> Thanks for your help.
> Don
>|||In addition to Andrew's comments, please read:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:246501c50239$11fd94f0$a601280a@.phx.gbl...
> SQL 7.0 NT 2000 server
> Before doing anything:
> Used: Free: Total
> 20GB 20GB 40GB
> After
> dbcc shrinkfile(1,notruncate)
> followed by
> dbcc shrinkfile(1,truncateonly)
> the view from EM is:
> Used: Free: Total
> 20GB 1.6MB 21GB
> Within 24 hours
> Used: Free: Total
> 20GB 12GB 32GB
> The db and trans log are set to grow automatically by 10%
> so how can the MDF file suddenly go to 32GB? Shouldn't
> SQL expand the 1.6MB by 10% when needed, and then 10%
> after that, etc?
> There have been no deletions in the DB only insertions.
> Thanks for your help.
> Don
>|||Yes, there was an indexing maintenance job that ran.
As you say the first growth would be 2GB, but wouldn't
SQL use that space and then increase it by 10%?
Don
>--Original Message--
>The growth is most likely due to a reindexing job
(probably a maintenance
>plan) at night. The growth is 10% of the entire file
and not the 1.6MB
>free. So the first growth would be 2GB and it just gets
larger from there.
>When you have a db over a few GB it is a good idea to
change the growth
>increment to a fixed amount and not a percentage so it
always grows in a
>controllable fashion. But never shrink an active
database down to anywhere
>near the size of the data. The db needs lots of free
space to work
>properly.
>--
>Andrew J. Kelly SQL MVP
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:246501c50239$11fd94f0$a601280a@.phx.gbl...
>> SQL 7.0 NT 2000 server
>> Before doing anything:
>> Used: Free: Total
>> 20GB 20GB 40GB
>> After
>> dbcc shrinkfile(1,notruncate)
>> followed by
>> dbcc shrinkfile(1,truncateonly)
>> the view from EM is:
>> Used: Free: Total
>> 20GB 1.6MB 21GB
>> Within 24 hours
>> Used: Free: Total
>> 20GB 12GB 32GB
>> The db and trans log are set to grow automatically by
10%
>> so how can the MDF file suddenly go to 32GB? Shouldn't
>> SQL expand the 1.6MB by 10% when needed, and then 10%
>> after that, etc?
>> There have been no deletions in the DB only insertions.
>> Thanks for your help.
>> Don
>>
>
>.
>|||If you went from 20 GB to 32 GB, then my guess is it had to autogrow
MULTIPLE times.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:214101c5023c$620efd50$a401280a@.phx.gbl...
> Yes, there was an indexing maintenance job that ran.
> As you say the first growth would be 2GB, but wouldn't
> SQL use that space and then increase it by 10%?
> Don
> >--Original Message--
> >The growth is most likely due to a reindexing job
> (probably a maintenance
> >plan) at night. The growth is 10% of the entire file
> and not the 1.6MB
> >free. So the first growth would be 2GB and it just gets
> larger from there.
> >When you have a db over a few GB it is a good idea to
> change the growth
> >increment to a fixed amount and not a percentage so it
> always grows in a
> >controllable fashion. But never shrink an active
> database down to anywhere
> >near the size of the data. The db needs lots of free
> space to work
> >properly.
> >
> >--
> >Andrew J. Kelly SQL MVP
> >
> >
> >"Don" <anonymous@.discussions.microsoft.com> wrote in
> message
> >news:246501c50239$11fd94f0$a601280a@.phx.gbl...
> >> SQL 7.0 NT 2000 server
> >>
> >> Before doing anything:
> >>
> >> Used: Free: Total
> >> 20GB 20GB 40GB
> >>
> >> After
> >> dbcc shrinkfile(1,notruncate)
> >> followed by
> >> dbcc shrinkfile(1,truncateonly)
> >> the view from EM is:
> >>
> >> Used: Free: Total
> >> 20GB 1.6MB 21GB
> >>
> >> Within 24 hours
> >>
> >> Used: Free: Total
> >> 20GB 12GB 32GB
> >>
> >> The db and trans log are set to grow automatically by
> 10%
> >> so how can the MDF file suddenly go to 32GB? Shouldn't
> >> SQL expand the 1.6MB by 10% when needed, and then 10%
> >> after that, etc?
> >> There have been no deletions in the DB only insertions.
> >>
> >> Thanks for your help.
> >>
> >> Don
> >>
> >>
> >
> >
> >.
> >|||Yes as Aaron stated it had to have grown multiple times. Since a DBREINDEX
needs 1.2 (or more) times the size of the index in free space to rebuild it
this is not uncommon. If your db has 20 GB of actual data and indexes I see
no problem with have 10 or 20GB of free space in the data file. There is no
penalty for too much free space but a big one for too little.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:214101c5023c$620efd50$a401280a@.phx.gbl...
> Yes, there was an indexing maintenance job that ran.
> As you say the first growth would be 2GB, but wouldn't
> SQL use that space and then increase it by 10%?
> Don
>>--Original Message--
>>The growth is most likely due to a reindexing job
> (probably a maintenance
>>plan) at night. The growth is 10% of the entire file
> and not the 1.6MB
>>free. So the first growth would be 2GB and it just gets
> larger from there.
>>When you have a db over a few GB it is a good idea to
> change the growth
>>increment to a fixed amount and not a percentage so it
> always grows in a
>>controllable fashion. But never shrink an active
> database down to anywhere
>>near the size of the data. The db needs lots of free
> space to work
>>properly.
>>--
>>Andrew J. Kelly SQL MVP
>>
>>"Don" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:246501c50239$11fd94f0$a601280a@.phx.gbl...
>> SQL 7.0 NT 2000 server
>> Before doing anything:
>> Used: Free: Total
>> 20GB 20GB 40GB
>> After
>> dbcc shrinkfile(1,notruncate)
>> followed by
>> dbcc shrinkfile(1,truncateonly)
>> the view from EM is:
>> Used: Free: Total
>> 20GB 1.6MB 21GB
>> Within 24 hours
>> Used: Free: Total
>> 20GB 12GB 32GB
>> The db and trans log are set to grow automatically by
> 10%
>> so how can the MDF file suddenly go to 32GB? Shouldn't
>> SQL expand the 1.6MB by 10% when needed, and then 10%
>> after that, etc?
>> There have been no deletions in the DB only insertions.
>> Thanks for your help.
>> Don
>>
>>
>>.|||I seem to be missing the concept...
if I shrink a db down to the size of the data, and SQL
needs lots of free space to work with, won't SQL either
expand by a fixed amount or by percentage as needed?
Don
>--Original Message--
>The growth is most likely due to a reindexing job
(probably a maintenance
>plan) at night. The growth is 10% of the entire file
and not the 1.6MB
>free. So the first growth would be 2GB and it just gets
larger from there.
>When you have a db over a few GB it is a good idea to
change the growth
>increment to a fixed amount and not a percentage so it
always grows in a
>controllable fashion. But never shrink an active
database down to anywhere
>near the size of the data. The db needs lots of free
space to work
>properly.
>--
>Andrew J. Kelly SQL MVP
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:246501c50239$11fd94f0$a601280a@.phx.gbl...
>> SQL 7.0 NT 2000 server
>> Before doing anything:
>> Used: Free: Total
>> 20GB 20GB 40GB
>> After
>> dbcc shrinkfile(1,notruncate)
>> followed by
>> dbcc shrinkfile(1,truncateonly)
>> the view from EM is:
>> Used: Free: Total
>> 20GB 1.6MB 21GB
>> Within 24 hours
>> Used: Free: Total
>> 20GB 12GB 32GB
>> The db and trans log are set to grow automatically by
10%
>> so how can the MDF file suddenly go to 32GB? Shouldn't
>> SQL expand the 1.6MB by 10% when needed, and then 10%
>> after that, etc?
>> There have been no deletions in the DB only insertions.
>> Thanks for your help.
>> Don
>>
>
>.
>|||But, that's my question...
why would it expand MULTIPLE times? It's only supposed to
expand as needed...unless I'm missing something entirely.
>--Original Message--
>If you went from 20 GB to 32 GB, then my guess is it had
to autogrow
>MULTIPLE times.
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"Don" <anonymous@.discussions.microsoft.com> wrote in
message
>news:214101c5023c$620efd50$a401280a@.phx.gbl...
>> Yes, there was an indexing maintenance job that ran.
>> As you say the first growth would be 2GB, but wouldn't
>> SQL use that space and then increase it by 10%?
>> Don
>> >--Original Message--
>> >The growth is most likely due to a reindexing job
>> (probably a maintenance
>> >plan) at night. The growth is 10% of the entire file
>> and not the 1.6MB
>> >free. So the first growth would be 2GB and it just
gets
>> larger from there.
>> >When you have a db over a few GB it is a good idea to
>> change the growth
>> >increment to a fixed amount and not a percentage so it
>> always grows in a
>> >controllable fashion. But never shrink an active
>> database down to anywhere
>> >near the size of the data. The db needs lots of free
>> space to work
>> >properly.
>> >
>> >--
>> >Andrew J. Kelly SQL MVP
>> >
>> >
>> >"Don" <anonymous@.discussions.microsoft.com> wrote in
>> message
>> >news:246501c50239$11fd94f0$a601280a@.phx.gbl...
>> >> SQL 7.0 NT 2000 server
>> >>
>> >> Before doing anything:
>> >>
>> >> Used: Free: Total
>> >> 20GB 20GB 40GB
>> >>
>> >> After
>> >> dbcc shrinkfile(1,notruncate)
>> >> followed by
>> >> dbcc shrinkfile(1,truncateonly)
>> >> the view from EM is:
>> >>
>> >> Used: Free: Total
>> >> 20GB 1.6MB 21GB
>> >>
>> >> Within 24 hours
>> >>
>> >> Used: Free: Total
>> >> 20GB 12GB 32GB
>> >>
>> >> The db and trans log are set to grow automatically
by
>> 10%
>> >> so how can the MDF file suddenly go to 32GB?
Shouldn't
>> >> SQL expand the 1.6MB by 10% when needed, and then
10%
>> >> after that, etc?
>> >> There have been no deletions in the DB only
insertions.
>> >>
>> >> Thanks for your help.
>> >>
>> >> Don
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||> why would it expand MULTIPLE times? It's only supposed to
> expand as needed...unless I'm missing something entirely.
Who knows what your maintenance plan did, and what other activity is going
on in the box?
As for how many times it will expand, if you set it to 10%, and it needs a
GB, then another GB, then another GB, then another GB, it is going to grow
multiple times. Since we have ABSOLUTELY no idea what was going on in your
system, I think it's impossible for us to tell you how many times the file
needed to grow and why.
A|||Seems to me that the LOG file is supposed to be used for
these types of things.
>--Original Message--
>> why would it expand MULTIPLE times? It's only
supposed to
>> expand as needed...unless I'm missing something
entirely.
>Who knows what your maintenance plan did, and what other
activity is going
>on in the box?
>As for how many times it will expand, if you set it to
10%, and it needs a
>GB, then another GB, then another GB, then another GB,
it is going to grow
>multiple times. Since we have ABSOLUTELY no idea what
was going on in your
>system, I think it's impossible for us to tell you how
many times the file
>needed to grow and why.
>A
>
>.
>|||> Seems to me that the LOG file is supposed to be used for
> these types of things.
Seems you should get a better grasp on what you are doing in your
maintenance plan. For example, if you reindex a table, it needs to allocate
data to other areas within the DATA file. Why would you think this should
happen exclusively in the LOG file?
If you don't have the ability or motivation to understand why you need more
disk space in this case, buy a bigger drive and stop shrinking your database
for no reason (and without understanding the causes/consequences).|||What a jerk...
>--Original Message--
>> Seems to me that the LOG file is supposed to be used
for
>> these types of things.
>Seems you should get a better grasp on what you are
doing in your
>maintenance plan. For example, if you reindex a table,
it needs to allocate
>data to other areas within the DATA file. Why would you
think this should
>happen exclusively in the LOG file?
>If you don't have the ability or motivation to
understand why you need more
>disk space in this case, buy a bigger drive and stop
shrinking your database
>for no reason (and without understanding the
causes/consequences).
>
>.
>|||This is the kind of immature crap that makes me wonder why I bother to help
anyone at all.
"Oh geez, you're right, but I don't like your tone. So instead of thanking
you and taking your advice, I'm going to call you names!"
*plonk*
> What a jerk...|||Yes Don it will but you obviously needed more space than one growth block
gave you. Once it used up that amount it needed to grow again. In your
case it must have grown several times. You can trace the Autogrow event if
you want to see when it grows and how many times. This is very common with
database maintenance activities.
--
Andrew J. Kelly SQL MVP
"Don" <anonymous@.discussions.microsoft.com> wrote in message
news:21b901c50243$66ce07d0$a401280a@.phx.gbl...
>I seem to be missing the concept...
> if I shrink a db down to the size of the data, and SQL
> needs lots of free space to work with, won't SQL either
> expand by a fixed amount or by percentage as needed?
> Don
>>--Original Message--
>>The growth is most likely due to a reindexing job
> (probably a maintenance
>>plan) at night. The growth is 10% of the entire file
> and not the 1.6MB
>>free. So the first growth would be 2GB and it just gets
> larger from there.
>>When you have a db over a few GB it is a good idea to
> change the growth
>>increment to a fixed amount and not a percentage so it
> always grows in a
>>controllable fashion. But never shrink an active
> database down to anywhere
>>near the size of the data. The db needs lots of free
> space to work
>>properly.
>>--
>>Andrew J. Kelly SQL MVP
>>
>>"Don" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:246501c50239$11fd94f0$a601280a@.phx.gbl...
>> SQL 7.0 NT 2000 server
>> Before doing anything:
>> Used: Free: Total
>> 20GB 20GB 40GB
>> After
>> dbcc shrinkfile(1,notruncate)
>> followed by
>> dbcc shrinkfile(1,truncateonly)
>> the view from EM is:
>> Used: Free: Total
>> 20GB 1.6MB 21GB
>> Within 24 hours
>> Used: Free: Total
>> 20GB 12GB 32GB
>> The db and trans log are set to grow automatically by
> 10%
>> so how can the MDF file suddenly go to 32GB? Shouldn't
>> SQL expand the 1.6MB by 10% when needed, and then 10%
>> after that, etc?
>> There have been no deletions in the DB only insertions.
>> Thanks for your help.
>> Don
>>
>>
>>.