TempDB does not release space after restart

Anashwar N 41 Reputation points
2020-09-07T16:24:27.42+00:00

Hi Team, 

      In one of our Production Environment we had a tempdb growth due to bulk transaction and occupied the drive space , we were not able to shrink the datafile and release space , there were no active transactions on tempdb , we tried clearing cache and tried to shrink,that also didn't worked , we were getting an error the 'worktable was on hold', so went ahead and restarted sql server , but after restart also the space was not released , but i was able to shrink and release space

any thoughts why tempdb did not released space to the initial size of tempdb after sql restart.

Regards,

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,620 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Shashank Singh 6,246 Reputation points
    2020-09-07T16:43:29.46+00:00

    After restart the tempdb file has defaulted to the size it was last modified to. In this case you need to shrink the tempdb data files. 

    USE [Tempdb]
GO
DBCC SHRINKFILE (N'Tempdev', TRUNCATEONLY)
GO

    If this does not works try simple shrink 

      USE [Tempdb]
    GO
    DBCC SHRINKFILE (N'Tempdev')
    GO
    0 comments
  2. CathyJi-MSFT 21,081 Reputation points Microsoft Vendor
    2020-09-08T02:34:11.987+00:00

    Hi @Anonymous ,

    >any thoughts why tempdb did not released space to the initial size of tempdb after sql restart.

    When SQL Server starts, tempdb is re-created by using a copy of the model database, and tempdb is reset to its last configured size. The configured size is the last explicit size that was set by using a file size changing operation such as ALTER DATABASE that uses the MODIFY FILE option or the DBCC SHRINKFILE or DBCC SHRINKDATABASE statements.

    The current tempdb database size can be found by querying DMV tempdb.sys.database_files.
    The last manually configured tempdb database size can be found by querying DMV sys.master_files.

    Refer to How to shrink the tempdb database in SQL Server and Tempdb size resets after a SQL Server service restart to get more detail information.

    Best regard,
    Cathy

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.