0

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 '15 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 '15 at 8:31
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. – Zerotoinfinity May 11 '15 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 '15 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 – Zerotoinfinity May 11 '15 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 '15 at 8:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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