Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL temp file is gigantic

Posted on 2013-01-21
13
Medium Priority
?
487 Views
Last Modified: 2013-01-23
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
0
Comment
Question by:Damian_Gardner
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
  • +2
13 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 38803481
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38803484
the easiest option to shrink will be to restart the sql server service if possible
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38803496
By the way I am assuming that you have already discovered why it increased in size and have corrected that process.
0
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 35

Expert Comment

by:David Todd
ID: 38803536
Hi,

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

Regards
  David
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 38803589
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
 
LVL 43

Expert Comment

by:Eugene Z
ID: 38803598
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
 

Author Comment

by:Damian_Gardner
ID: 38807078
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
 

Author Comment

by:Damian_Gardner
ID: 38807124
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
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 38807216
yes, that will be the initial size of your tempdb file when the service starts
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38807286
You need to use Method 1 from the link I posted in the first comment.
0
 

Author Closing Comment

by:Damian_Gardner
ID: 38807525
Thanks for your help. Just needed to adjust the initial size, and restart SQL.  thanks
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38808258
Don't forget to find out the cause, otherwise you may be back to where you started from here very soon.
0
 

Author Comment

by:Damian_Gardner
ID: 38810731
yes - thanks.  appreciate it.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question