Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1109

Re: tempdb is huge

Show all headers | View raw

tbone (tony.despain@gmail.com) writes:
> in an attempt to keep it from getting to big I set the size to what it
> was currently.  I then removed the tempdb.mdf file so it would get
> recreated, now I can't even connect...is that because it is recreating
> the 200 GB .mdf file?  Did I just screw it up? 
> 

tempdb.mdf is recreated every time you restart SQL Server. But I guess
it reuses the existing file. If you remove the file, the file has to
be initiated; I don't think there ever is Instant File Initialisation
on SQL 2000. Zeroing out 200 GB takes some time.

What do you see in the SQL Server errorlog?

Why you tempdb file is so big I cannot say, since I don't know your
system, but tempdb is used for more than just temp tables, for instance
work tables for queries, and spill area for sorting and hashing. Bad
queries can cause your tempdb to grow.

Exactly which command did you run to change the size of tempdb?



-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

tempdb is huge tbone <tony.despain@gmail.com> - 2012-06-07 10:46 -0700
  Re: tempdb is huge tbone <tony.despain@gmail.com> - 2012-06-07 11:47 -0700
    Re: tempdb is huge Erland Sommarskog <esquel@sommarskog.se> - 2012-06-07 21:34 +0200
      Re: tempdb is huge tbone <tony.despain@gmail.com> - 2012-06-07 13:06 -0700
  Re: tempdb is huge tbone <tony.despain@gmail.com> - 2012-06-07 13:54 -0700

csiph-web