SQL Server – Why TempDB and Log File Size Are Not Reduced

sql serversql-server-2012tempdbtransaction-log

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

Best Answer

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.