SQL temp file is gigantic

Hello - we're trying unsuccessfully to shrink the Master Temp DB file.  What's the trick to getting this down in size?  It's over 200GB on a 400GB partiition.  It's SQL 2008 on a Windows 2008 server.

Thanks
Damian_GardnerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Anthony PerkinsConnect With a Mentor Commented:
The best plan is to restart the SQL Server service when you have a window of inactivity.  If that is not an option here are suggestions from MSDN:
How to shrink the tempdb database in SQL Server
http://support.microsoft.com/kb/307487
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
the easiest option to shrink will be to restart the sql server service if possible
0
 
Anthony PerkinsCommented:
By the way I am assuming that you have already discovered why it increased in size and have corrected that process.
0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
David ToddSenior DBACommented:
Hi,

Do you have rowversioning turned on, or similar new features? Some of these new features do hammer the tempdb.

Regards
  David
0
 
Eugene ZCommented:
check what process(s) is (are)
running and using tempdb

by stopping it(them)  - you may resolve this problems without downtime
try sp_who /sp_wh2


also check

How to shrink the tempdb database in SQL Server
http://support.microsoft.com/kb/307487

Optimizing tempdb Performance
http://msdn.microsoft.com/en-us/library/ms175527(v=sql.105).aspx
0
 
Eugene ZCommented:
also

<You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space used by the user objects, internal objects, and version stores in the tempdb files. Additionally, to monitor the page allocation or deallocation activity in tempdb at the session or task level, you can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views. These views can be used to identify large queries, temporary tables, or table variables that are using a large amount of tempdb disk space.>

more:
Troubleshooting Insufficient Disk Space in tempdb
http://msdn.microsoft.com/en-us/library/ms176029(v=sql.105).aspx
0
 
Damian_GardnerAuthor Commented:
sorry for the delay, gentlemen - got sidetracked yesterday, and am getting back to this now.  I don't know what is causing it to grow, but will try and find out.  I have rebooted and restarted the service several times, with no change to the size, so there's something else going on.  Standby for more on this...
0
 
Damian_GardnerAuthor Commented:
I've noticed the Initial Size parameter on the "tempdev.mdf" file is 207,000 MB.  Is this why restarting SQL isn't helping? it's simply resetting it back to that size?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
yes, that will be the initial size of your tempdb file when the service starts
0
 
Anthony PerkinsCommented:
You need to use Method 1 from the link I posted in the first comment.
0
 
Damian_GardnerAuthor Commented:
Thanks for your help. Just needed to adjust the initial size, and restart SQL.  thanks
0
 
Anthony PerkinsCommented:
Don't forget to find out the cause, otherwise you may be back to where you started from here very soon.
0
 
Damian_GardnerAuthor Commented:
yes - thanks.  appreciate it.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.