Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 tempdb not reducing to normal size after restart

Author  Topic 

funkycrab
Starting Member

1 Post

Posted - 2015-05-04 : 15:08:56
TFS 2013/SQL Server 2012

We had some issues with a TFS Database where the database log file and the tempdb were filling up our drive space.

I was able to detach the database successfully through TFS and SQL. I moved the rogue db to another machine and the large transactions finally completed successfully.

I moved the database back to the TFS server and successfully attached to SQL and TFS.

I restarted the machine expecting the large temdb would go back to the normal size but it did not shrink. Now I have a tempdb @127 GB that will not free up space after a restart.

Any suggestions on how/what to do to get this tempdb back to normal? The server is working normally but it alarms me that we this amount of wasted space in a tempdb that will not release.

Thanks in Advance,

Michael

Michael Sparks
michaelsparks.tn@gmail.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-05-04 : 16:05:40
If tempdb did not shrink after the restart, then its initial size is that big. You'll have to shrink it via DBCC SHRINKFILE to reclaim the space.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2015-05-05 : 01:35:26
there are some various indicators you can use to set the initial TempDB size such as work usage patterns and largest index size - some more notes about setting the TempDB initial size -
http://www.sqlserver-dba.com/2014/01/tempdb-setting-the-initial-size.html

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page
   

- Advertisement -