With a series of question Part1, Part2 and Part3. With the answers and research on internet I came to know that

  • TemDb is use for holding different values (ex.: Temp variable, Temp table, CTE, Hash result, Hasg aggregation etc.)
  • Log is use for storing data which comes from buffer and then it is written to the disk and once it is done SQL clears log entry with CHECKPOINT.

In both cases, the result stored is temporary and SQL take care of removing entry from temp database or (any database) log file. So, at any given instance when SQL Server is not running (not running any user defined query or function) then both temdb and log file size should be automatically shrink to minimum. [Assuming that SQL Server has its own process which uses temdb and database log file] But when I see my log file, it shows me 80 GB and temdb as 45 GB when I am doing nothing on SQL Server.

So, why temdb and (all database) log file size does not reduce when query execution is done?

Also, how SQL Server query executes, I mean its workflow (ex.)

Query Execution > Buffer Pool > Buffer pool writes entry in Log file/ Buffer pool use temdb > log file writes to disk

  • Shrinking of both data and log file has to be done by user. SQL Server would never shrink data and log file on its own unless AUTO SHRINK is enabled which is pathetically bad.
    – Shanky
    May 11, 2015 at 7:08
  • Note that tempdb does not necessarily hold CTEs. A CTE is a logical query construct that describes the results. SQL Server may or may not choose to use tempdb.
    – Dan Guzman
    May 11, 2015 at 8:31

1 Answer 1


Shrinking a database is not a good idea. There are specific cases, but they require a DBA to carefully evaluate the situation.

Sql Server cannot have an idea if a query in, say, next five minutes is going to hit tempdb heavily. Without such a knowledge, shrinking and expanding tempdb is going to be unnecessary and, to be honest, stupid too.

TempDB is re-initialized whenever Sql Server service is restarted. In most of the cases, this effectively shrinks the tempdb data and log files.

  • Thanks for your answer. +1 But I have a question here, re-initialize is fine but doesn't SQL Server should remove data from temdb when it is done with running query. May 11, 2015 at 6:39
  • SQL Server returns space as unused space within tempdb when objects are no longer needed. The space will then be reused as needed. This avoids the need to constantly shrink and grow the physical files, similarly to user databases.
    – Dan Guzman
    May 11, 2015 at 8:36
  • But when I am done with queries, I believe my temdb size should come down. Now when my server is ideal, my temdb size is 48GB May 11, 2015 at 8:41
  • 1
    @Zerotoinfinity Sql Server does remove data from tempdb after it's done. The thing is, removing data from any database will not cause the db files to shrink. The space within db is just marked as unused.
    – vonPryz
    May 11, 2015 at 8:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.