Re: use of tempdb by union all

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 03/23/04 

Date: Tue, 23 Mar 2004 15:43:00 +0100

Chief,

> Apologies for "reviving" this thread once again

N problem.

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

I have no problems with trying to be precise, au contraire. That is why I
posted in the first place in this thread. :-)

Yes, there are special situation where space inside the user database are
used as a "scratch area". (Re)creating indexes is such a case, and a recent
enhancement to SQL Server is that we can specify tempdb to be used for this.
I suppose that there might be other cases as well, although I can't think of
any at the moment.

It all boils down to the interpretation of the original question. SQL Server
will not use space from the user database for normal query processing (work
tables used as intermediate steps as the query is processed). There is at
least one special situation where SQL Server will use space inside the user
database as a type of work-area, and that is index (re)builds.
Is above OK? :-) 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Tenaya" <ct@ct.ct> wrote in message
news:uYNb7sNEEHA.700@TK2MSFTNGP09.phx.gbl...
> 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
    ... SQL Server will indeed allocate "scratch space" from the user database, ... >> It was my impression that the tempdb database will be used when SQL ...
    (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)
  • 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: How to Move SQL System Databases
    ... That should work Assuming that you shut down SQL Server for the file copy, ... > Assuming I've successfully copied the entire database SAN ... > system and user database data files are located) from the ...
    (microsoft.public.sqlserver.server)
  • Re: SQL Query problem take ages to run
    ... SQL Server will allocate space in TempDB, ... database, and the target database transaction log. ... If you allow SQL Server to perform the allocations as needed, ...
    (microsoft.public.sqlserver.programming)
Loading