Re: use of tempdb by union all

From: Tenaya (ct_at_ct.ct)
Date: 03/23/04 

Date: Tue, 23 Mar 2004 08:32:01 -0500

Tibor,

Apologies for "reviving" this thread once again, but I got to thinking a bit
more about your statement, and I'm now inclined to think that the statement
that "SQL will not allocate 'scratch space' from the user database" might be
misleading.

Earlier in this thread, I referenced the BOL section "tempdb and Index
Creation", and I'd draw your attention to that article again. As I read it,
SQL Server will indeed allocate "scratch space" from the user database,
since otherwise it seems to me that the article makes little sense.

I will certainly readily admit that the statement that drew your attention
in my posting was at best imprecise, and, quite conceivably, completely
wrong. Certainly one can argue that I was saying that if SQL Server needs
"scratch" disk space within the user database, and is unable to allocate the
space, that SQL Server will then automatically take the necessary "scratch"
disk space from the tempdb database. That is, I admit, completely wrong.

However, I think that without some qualification, your posting can be
construed as misleading.

BTW, I trust that both you and Andrew recognize that precision in wording is
necessary, since otherwise people may jump to incorrect conclusions.

Chief Tenaya

"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:ehywMdEEEHA.1228@TK2MSFTNGP11.phx.gbl...
> > It was my impression that the tempdb database will be used when SQL
Server
> > has insufficient memory / disk space to carry out the operation in the
> > current database ... i.e., the one in which the operation was "launched"
> > (via the USE command, either implicitly or explicitly).
>
> SQL Server will not allocate "scratch space" from the user database. If
such
> is needed, it is allocated in tempdb.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
>

Relevant Pages

  • Re: use of tempdb by union all
    ... there are special situation where space inside the user database are ... enhancement to SQL Server is that we can specify tempdb to be used for this. ... > that "SQL will not allocate 'scratch space' from the user database" might ...
    (microsoft.public.sqlserver.programming)
  • RE: NT4 Migration
    ... My understanding is you want to restructure the NT4 domain into a AD ... I know your major concern is move SQL server 2000 to new domain. ... Basically if you enabled SID history feature in AD, ... Detached all the user Database. ...
    (microsoft.public.windows.server.migration)
  • Re: use of tempdb by union all
    ... It was my impression that the tempdb database will be used when SQL Server ... the section "tempdb and Index Creation" in the SQL Server 2000 ... I think it might be misleading to imply that the tempdb database area is the ...
    (microsoft.public.sqlserver.programming)
  • SQL server Windows Authentication is Broken
    ... I am running SQL server 2000 on Windows 2000 in windows authentication mode. ... There is only one user database on the SQL instance. ... database role that has permissions only for few tables in the user database. ...
    (microsoft.public.sqlserver.security)
  • Re: Merge error 2147200925 - why does reboot the server machine could solve problem.
    ... but the problem occurs 'in certain circimstances' and ... relates to teh production of temporary tables, ... the tempdb database might play a part. ... Paul Ibison SQL Server MVP, ...
    (microsoft.public.sqlserver.replication)