SQL Server – Will Temp DB Space Allocated Be Reclaimed?

disk-spacesql serversql-server-2008-r2tempdb

I have observed that the tempdb in the production environment has grown over to 100 GB after initializing it.

I understand that due to server load and some expensive queries have resulted to the growth of tempdb in a span of just two days. I have been observing this over a week now and find this size did not grow further and remains the same.

I have one mdf file configured for tempdb on the server having 8 logical cores. Though I could add multiple mdf files based on the logical cores.

I observe that the free page size and the physical size of temp db data file are the same in my case

SELECT SUM(unallocated_extent_page_count) AS [free pages], 
(SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage;

Help me understand if this physical space would ever be reclaimed automatically without we explicitly executing script to shrink the space or by restarting the server to reset the temp db allocation??

Best Answer

Like all databases, free (unallocated) space in tempdb is reused for new objects. No action is needed for this space to be reused.

The physical size of tempdb files will reflect the high-water mark of growth since the instance was last started. The size will revert back to the initial file size after an instance restart, freeing space on the disk volume if growth occurred. It is also possible to release unused file space using DBCC SHRINKFILE. That should not be done routinely but may be desirable if the growth was an anomaly, such as due to a bad query. It is best to size files to accommodate expected space requirements and provide growth as a safety net for unexpected needs.

Multiple tempdb data files of equal size and growth can improve performance by reducing allocation page contention for some workloads. However, that is unrelated to space reclamation.

Related Solutions

Sql-server – Shrink Database Did Not Reclaim Much Space

When using the DBCC SHRINKDATABASE statement, you cannot shrink a whole database to be smaller than its original size. Therefore, if a database was created with a size of 10 MB and grew to 100 MB, the smallest the database could be reduced to is 10 MB, even if all the data in the database has been deleted.

However, you can shrink the individual database files to a smaller size than their initial size by using the DBCC SHRINKFILE statement. You must shrink each file individually, instead of trying to shrink the whole database.

Sql-server – ‘Tempdb’ is full warning message in SQL Server 2005

Without a suitable index, SQL Server has to perform a full sort of the data to satisfy your query. You can verify this is occurring by looking for a Sort or Top N Sort operator in the execution plan:

Sort

A suitable covering index that would avoid the need to sort in your query is:

CREATE NONCLUSTERED INDEX nc1
ON dbo.total (T_Date)
INCLUDE (T_idx, T_TAG);

When an explicit sort is needed, if SQL Server does not (or cannot) allocate sufficient memory to perform the sort entirely in memory, it will use tempdb. The allocation of sort run space in tempdb is a logged operation, which directly contributes to tempdb log space usage.

In addition, the sort starts a system transaction in tempdb to ensure sort allocations are correctly undone in case the transaction aborts. This open transaction can prevent tempdb log space being reused until the sort completes. Executing DBCC OPENTRAN (tempdb) may show an open transaction with sort_init in the description.

You should also review the SQL Server product documentation and this article to determine how to configure your tempdb database optimally for your workload.

Best Answer

Related Solutions

Sql-server – Shrink Database Did Not Reclaim Much Space

Sql-server – ‘Tempdb’ is full warning message in SQL Server 2005

Related Question