Could not allocate new page for database 'TEMPDB'.

I recently had a routine using a table variable involved in a fairly
complex query using a number of fairly large tables which ended with
the following error.
Server: Msg 1101, Level 17, State 10, Line 40
Could not allocate new page for database 'TEMPDB'. There are no more
pages
available in filegroup DEFAULT. Space can be created by dropping
objects,
adding additional files, or allowing file growth.
The tempdb is on autogrow.
The drive on which the tempdb resided was filling up after running for
a number of minutes. The drive had a couple of GB of space left until
the routine started. I looked into it a bit and read that temp
tables were better performers with larger tables than table vars so
tried using a temp table instead. The routine finished in a timely
manner without expanding the tempdb significantly from it's previous
size before the routines initial run(rebuilt tempdb). Any idea why the
big difference in performance and effect on the tempdb? The rest of
the routine remainded the same with the exception of the table var -->
temp table.
ThanksUse of temp tables and table v ariables will affect the query plans used
in a procedure.
It is likely that the use of the table variables has resulted in a query
plan that involved heavy use of worktables i.e sorting and thus the filling
up of tempdb.
If you can post the query plans for the different code (and the code) it
migth help to diagnose the problem.

> I recently had a routine using a table variable involved in a fairly
> complex query using a number of fairly large tables which ended with
> the following error.
> Server: Msg 1101, Level 17, State 10, Line 40
> Could not allocate new page for database 'TEMPDB'. There are no more
> pages
> available in filegroup DEFAULT. Space can be created by dropping
> objects,
> adding additional files, or allowing file growth.
> The tempdb is on autogrow.
> The drive on which the tempdb resided was filling up after running for
> a number of minutes. The drive had a couple of GB of space left until
> the routine started. I looked into it a bit and read that temp
> tables were better performers with larger tables than table vars so
> tried using a temp table instead. The routine finished in a timely
> manner without expanding the tempdb significantly from it's previous
> size before the routines initial run(rebuilt tempdb). Any idea why
> the
> big difference in performance and effect on the tempdb? The rest of
> the routine remainded the same with the exception of the table var -->
> temp table.
> Thanks
>|||I took a look at the query plans, they are identical. Odd thing is
that this used to run on the same environment in a timely manner using
the table var a w ago. Finding out the truth here may be difficult
as our application is being hosted by a client and we do not have
security permission to view their sensitive data.
Thanks for the post!

Cost of development for MSSQLS vs. Oracle

Many thanks to all who posted to my query regarding MSSQLS vs. Oracle.
You comments have been of great use.
We are fairly from the responses to the thread, that MSSQLS is both the
easiest to maintain and develop with.
MSSQLS users go Hooray !
ORACLE users go BOO!
:-)
What points can we make to our client to establish for them that development
on the Oracle platform would be more expensive than developing an MSSQLS
application ?
ADO is native to Microsoft products and therefore easier ?
Integrated security with NT is better ?
Server Administration is easier ?
Thanks again folks, it really is much appreciated.
Alex, England
Alex Stevens wrote:
> What points can we make to our client to establish for them that development
> on the Oracle platform would be more expensive than developing an MSSQLS
> application ?
Send them to Monster and tell them to look at the salary ranges for
either discipline...
D.
|||"Alex Stevens" <alex@.matrixinfotech.co.uk> wrote in message news:W_mt5.6906$ly4.33806@.NewsReader...
> Many thanks to all who posted to my query regarding MSSQLS vs. Oracle.
> You comments have been of great use.
> We are fairly from the responses to the thread, that MSSQLS is both the
> easiest to maintain and develop with.
> MSSQLS users go Hooray !
> ORACLE users go BOO!
> :-)
> What points can we make to our client to establish for them that development
> on the Oracle platform would be more expensive than developing an MSSQLS
> application ?
> ADO is native to Microsoft products and therefore easier ?
> Integrated security with NT is better ?
> Server Administration is easier ?
> Thanks again folks, it really is much appreciated.
> Alex, England
>
Alex,
I have developed with database apps with ado using MSAccess, MSSQL, ORACLE and db2. I really don't think there is much of a
difference between MSSQL and Oracle when it comes to application development. ADO is ADO. ANSI SQL is ANSI SQL (assuming you are
at 8i or higher, with 9i providing standard join syntax). Code for calling an SP and using the data is the same. I think it is a
wash when it comes to development.
The real question is on of total cost of ownership. Here, MSSQL probably has an edge. You need to look at hardware, licensing,
DBA's, backup and recovery, replication, etc. in order to justify one over the other with respect to true cost of ownership.
Al Reid
How will I know when I get there...
If I don't know where I'm going?

Cost of development for MSSQLS vs. Oracle

Many thanks to all who posted to my query regarding MSSQLS vs. Oracle.
You comments have been of great use.
We are fairly from the responses to the thread, that MSSQLS is both the
easiest to maintain and develop with.
MSSQLS users go Hooray !
ORACLE users go BOO!
:-)
What points can we make to our client to establish for them that development
on the Oracle platform would be more expensive than developing an MSSQLS
application ?
ADO is native to Microsoft products and therefore easier ?
Integrated security with NT is better ?
Server Administration is easier ?
Thanks again folks, it really is much appreciated.
Alex, England
Alex Stevens wrote:
> What points can we make to our client to establish for them that development
> on the Oracle platform would be more expensive than developing an MSSQLS
> application ?
Send them to Monster and tell them to look at the salary ranges for
either discipline...
D.