estimating tempdb usage

Is there any way to estimate how much space will be needed by tempdb?
I have a user executing a simple query similar to:
select orders.customerid, sum(quantity*unitprice) as amount
from orders inner join [order details]
on orders.orderid=[order details].orderid
group by orders.customerid
In other words, two thin (not too many columns) tables, equi-join,
summarizing and a group by. The only trouble is her two tables have
28000 rows and nearly 800 million rows. Her query dies after a few
hours when tempdb autogrows and runs out of disk space at 17 gig. I
know tempdb is used for work tables, joins, sorts, group bys, etc.
But knowing the columns sizes and number of rows, is there a way to
estimate how much tempdb will be needed? She's basically the only
user on the system.
By the way, this is SQL Server 2000.
Thanks,
ScottI guess it would be better to Grow the tempdb first, then run oyur query.
I had the same problem on SQL Server 6.5 to 2000 migration with the log
files (we had big tables as well) , the Autogrowth didn´t work. So we had to
create the database and transaction logs big enough to support migration
process before initiate it.
HTH
"scott parmelee" <s_parmelee@.hotmail.com> escreveu na mensagem
news:e14a8116.0311130819.725c0a4@.posting.google.com...
> Is there any way to estimate how much space will be needed by tempdb?
> I have a user executing a simple query similar to:
> select orders.customerid, sum(quantity*unitprice) as amount
> from orders inner join [order details]
> on orders.orderid=[order details].orderid
> group by orders.customerid
> In other words, two thin (not too many columns) tables, equi-join,
> summarizing and a group by. The only trouble is her two tables have
> 28000 rows and nearly 800 million rows. Her query dies after a few
> hours when tempdb autogrows and runs out of disk space at 17 gig. I
> know tempdb is used for work tables, joins, sorts, group bys, etc.
> But knowing the columns sizes and number of rows, is there a way to
> estimate how much tempdb will be needed? She's basically the only
> user on the system.
> By the way, this is SQL Server 2000.
> Thanks,
> Scott

Errors 605, 823 and KB Article 826433

We are experiencing continuous 605/823 errors on tempdb. Having read KB
Article 826433 we want to set the trace flag for extended diags. However, it
appears we need a more recent version than SQL 2000 sp3a. The article
suggests build 847.
This being the case, please can someone explain how we go about obtaining
build 847.
Thanks
Graham,
The simple answer is to contact MS, as the KB suggests. Have you tried
moving tempdb to another, non caching local drive as the KB article
suggests. The article is also referenced by 836096. Have you tried using the
I/O Stress Simulator to see if you have hardware problems?
Chris Wood
Alberta Department of Energy
CANADA
"Graham G" <GrahamG@.discussions.microsoft.com> wrote in message
news:B7404237-5848-4C1D-A70B-FE41DB0524C6@.microsoft.com...
> We are experiencing continuous 605/823 errors on tempdb. Having read KB
> Article 826433 we want to set the trace flag for extended diags. However,
it
> appears we need a more recent version than SQL 2000 sp3a. The article
> suggests build 847.
> This being the case, please can someone explain how we go about obtaining
> build 847.
> Thanks