Damian Gardner
asked on
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
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
the easiest option to shrink will be to restart the sql server service if possible
By the way I am assuming that you have already discovered why it increased in size and have corrected that process.
Hi,
Do you have rowversioning turned on, or similar new features? Some of these new features do hammer the tempdb.
Regards
David
Do you have rowversioning turned on, or similar new features? Some of these new features do hammer the tempdb.
Regards
David
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
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
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_us age 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
<You can use the sys.dm_db_file_space_usage
more:
Troubleshooting Insufficient Disk Space in tempdb
http://msdn.microsoft.com/en-us/library/ms176029(v=sql.105).aspx
ASKER
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...
ASKER
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?
yes, that will be the initial size of your tempdb file when the service starts
You need to use Method 1 from the link I posted in the first comment.
ASKER
Thanks for your help. Just needed to adjust the initial size, and restart SQL. thanks
Don't forget to find out the cause, otherwise you may be back to where you started from here very soon.
ASKER
yes - thanks. appreciate it.