Does shrinking a user database cause tempdb to grow?

I'm looking at all the possible issues that may have contributed to an
inexplicably large jump in size for my tempdb. I recall reading an article
or posting that stated shrinking a user database will cause tempdb to grow.
Can anyone provide insight on this?Sorry, I misread "tempdb" as "log file" ... Disregard my previous reply
(although, you should still read that article anyway -- it's very
informative). I'm not aware of any condition that would cause tempdb to
grow during a shrink.
"Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an
article
> or posting that stated shrinking a user database will cause tempdb to
grow.
> Can anyone provide insight on this?
>|||Yes:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an
article
> or posting that stated shrinking a user database will cause tempdb to
grow.
> Can anyone provide insight on this?
>|||AFAIK, tempdb is not used for a shrink. The pages are quite simply moved toward the beginning of the
file.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rod Bautista" <rod.bautista@.cox.net> wrote in message news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an article
> or posting that stated shrinking a user database will cause tempdb to grow.
> Can anyone provide insight on this?
>|||Thanks Adam and Tibor.
I thought as much...I was certain somebody stated that in a post and the
idea stuck with me. It didn't make any sense for temdb to be affected
during a shrink operation but I had to throw the question out there.
R
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u$OUb8jqEHA.3464@.tk2msftngp13.phx.gbl...
> AFAIK, tempdb is not used for a shrink. The pages are quite simply moved
toward the beginning of the
> file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> > I'm looking at all the possible issues that may have contributed to an
> > inexplicably large jump in size for my tempdb. I recall reading an
article
> > or posting that stated shrinking a user database will cause tempdb to
grow.
> > Can anyone provide insight on this?
> >
> >
>|||I know others have replied with the same answer, but I'll also say that from
the code in shrink there's nothing that touches or affects tempdb.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an
article
> or posting that stated shrinking a user database will cause tempdb to
grow.
> Can anyone provide insight on this?
>

Does shrinking a user database cause tempdb to grow?

I'm looking at all the possible issues that may have contributed to an
inexplicably large jump in size for my tempdb. I recall reading an article
or posting that stated shrinking a user database will cause tempdb to grow.
Can anyone provide insight on this?
Yes:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
"Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an
article
> or posting that stated shrinking a user database will cause tempdb to
grow.
> Can anyone provide insight on this?
>
|||Sorry, I misread "tempdb" as "log file" ... Disregard my previous reply
(although, you should still read that article anyway -- it's very
informative). I'm not aware of any condition that would cause tempdb to
grow during a shrink.
"Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an
article
> or posting that stated shrinking a user database will cause tempdb to
grow.
> Can anyone provide insight on this?
>
|||AFAIK, tempdb is not used for a shrink. The pages are quite simply moved toward the beginning of the
file.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rod Bautista" <rod.bautista@.cox.net> wrote in message news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an article
> or posting that stated shrinking a user database will cause tempdb to grow.
> Can anyone provide insight on this?
>
|||Thanks Adam and Tibor.
I thought as much...I was certain somebody stated that in a post and the
idea stuck with me. It didn't make any sense for temdb to be affected
during a shrink operation but I had to throw the question out there.
R
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u$OUb8jqEHA.3464@.tk2msftngp13.phx.gbl...
> AFAIK, tempdb is not used for a shrink. The pages are quite simply moved
toward the beginning of the
> file.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
article[vbcol=seagreen]
grow.
>
|||I know others have replied with the same answer, but I'll also say that from
the code in shrink there's nothing that touches or affects tempdb.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rod Bautista" <rod.bautista@.cox.net> wrote in message
news:euzneqjqEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I'm looking at all the possible issues that may have contributed to an
> inexplicably large jump in size for my tempdb. I recall reading an
article
> or posting that stated shrinking a user database will cause tempdb to
grow.
> Can anyone provide insight on this?
>

Does it store all the results to tempdb database when I query against a large table which joins

Hi, all experts here,

I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.

With best regards,

Yours sincerely,

It doesn't store all the results - but it may store some intermediate results if it needs to.

You really shouldn't be putting tempdb on your local system drive. tempdb always has a lot of activity (typically the busiest database in any system), and ideally should be on its own disk - a fast one.

If you have over 4 million records in a single table, 1GB doesn't sound too big for tempdb. But I appreciate this doesn't fix your immediate problem.

Rob|||

Hi, Rob,

Thank you for your kind advices.

But how can we change the storage location of the tempdb database? It looks like tempdb database can not be detached.

I am looking forward to hearing from you further for your kind advices.

With best regards,

Yours sincerely,

|||

And also, though I have moved the data files of tempdb to other places, but when I restarted the database engine, the system automatically generates the data files of tempdb again back to its root data files?

Please give me some more advices for this.

Thanks a lot in advance.

With best regards,

Yours sincerely,

|||I believe moving tempdb is as simple as altering the file paths and restarting SQL Server. It'll create the new files automatically. I'm not sure if it removes the old ones automatically, so you may have to to some brief housekeeping once the database is up and running in its new location.

ALTER DATABASE tempdb MODIFY FILE (NAME = logical_filename, FILENAME = 'new_physical_file_path')

Just make sure you do the log in addition to all the data files you have.
|||

Hi, davidbrit2,

Thank you for your kind attention.

But I failed to modify the files paths for the data files of the tempdb database, I did restart the service, but the data files of tempdb again went back to its original file paths. Therefore I does not seem like we are able to change the file paths of the data files of tempdb.

Any more ideas? Thanks a lot in advance.

With best regards,

Yours sincerely,

|||You need to alter the database to tell it to have its files in a different location. But then you need to restart SQL Server (ie, the service - use SQL Configuration Manager) to actually have it start using those new locations. Once you've restarted SQL, you should see the new files start to be used. Then you can delete the old ones.

Hope this helps,

Rob|||

Hello Helen999888,

Let me start with giving you a brief of what is tempdb.
The tempdb system database is a global resource that is available to all users that are connected to an instance of SQL Server. The tempdb database is used to store the following objects: user objects, internal objects, and version stores.

User Objects

User objects are explicitly created by the user. These objects may be in the scope of a user session or in the scope of the routine in which the object is created. A routine is a stored procedure, trigger, or user-defined function. User objects can be one of the following:

User-defined tables and indexes
System tables and indexes
Global temporary tables and indexes
Local temporary tables and indexes
Table variables
Tables returned in table-valued functions
|||

Hi, HS_DJ,

Thanks a lot for your very kind and helpful advices. Got it done now.

With best regards,

Yours sincerely,

does importing a trace file via FN_TRACE_GETTABLE use the tempDB?

i need to look at some large trace files (3 - 8 Gig). The drive that
has the tempDB on is is small..currently has < 2 G of free space on
it.
does the FN_TRACE_GETTABLE() function route the import through the
tempDB?
if so is there a way to avoid this?
I don't want to choke the root drive of the server importing this...I have not seen it being spooled on a simple select into from a trace file
but you might just want to keep an eye on it.
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<kent@.kaeservices.com> wrote in message
news:29307ecd-75d3-42e1-ba4a-06d67f0c5c14@.d4g2000prg.googlegroups.com...
>i need to look at some large trace files (3 - 8 Gig). The drive that
> has the tempDB on is is small..currently has < 2 G of free space on
> it.
> does the FN_TRACE_GETTABLE() function route the import through the
> tempDB?
> if so is there a way to avoid this?
> I don't want to choke the root drive of the server importing this...

does importing a trace file via FN_TRACE_GETTABLE use the tempDB?

i need to look at some large trace files (3 - 8 Gig). The drive that
has the tempDB on is is small..currently has < 2 G of free space on
it.
does the FN_TRACE_GETTABLE() function route the import through the
tempDB?
if so is there a way to avoid this?
I don't want to choke the root drive of the server importing this...
I have not seen it being spooled on a simple select into from a trace file
but you might just want to keep an eye on it.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
<kent@.kaeservices.com> wrote in message
news:29307ecd-75d3-42e1-ba4a-06d67f0c5c14@.d4g2000prg.googlegroups.com...
>i need to look at some large trace files (3 - 8 Gig). The drive that
> has the tempDB on is is small..currently has < 2 G of free space on
> it.
> does the FN_TRACE_GETTABLE() function route the import through the
> tempDB?
> if so is there a way to avoid this?
> I don't want to choke the root drive of the server importing this...