June 23, 2011 at 7:45 am
my tempdb by default takes 98% of the space available on this disk, how can i alter that without restarting the service. also how do i know how much of the space reserved in tmepdb is really used?
Here is the result of sp_spaceused.
tempdb1955477.25 MB1900269.29 MB
46602328 KB43254328 KB3338320 KB9680 KB
June 23, 2011 at 8:04 am
Tara,
Here is a very good KB about shrinking tempdb.
http://support.microsoft.com/kb/307487
As you see Methods 1 and 3 require a restart.
The issue with Method 2 is that if your system is quiet and you sucessfully manage to shrink the database, when you restart SQL Server the database may expand again.
What I found recently was - tempdb was created at a certain size. Over time it was allowed to expand considerably. Also during this time, the server was restarted. When this happened, tempdb was recreated at this new size.
After applying Method 2, during a server maintenance the server was restarted. When this happened tempdb was recreated at the old size, prior to shrinking.
Personally, I find that Method 3 is the easiest to do. But in our case we have numerous application servers which are maintaining connections. Thus, when the server was brought up in single user mode, the app server made connection faster than I could. Teaches me to try a short cut;-)
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
June 23, 2011 at 12:56 pm
I think you can also run the report 'DiskUsage' for tempdb and it will show you stats on it.
June 23, 2011 at 12:58 pm
I strongly vote against just the shrinking of tempdb.
Tempdb didn't expand to that size without a reason. And, as long as the reason has not been found and taken care of, it's likely to expand again. And again. And again.
Starting with SS2K5, there are a lot of tools available to track down the root cause of temp db growth (some of them are covered in this article: http://technet.microsoft.com/de-de/library/cc966545%28en-us%29.aspx).
I'd rather analyze the root cause. Fix it, monitor the effect (e.g. by monitoring unallocated space). Shrink the size to a value large enough to cover the size required after the fix so you don't run into the file size growth immediately. Monitor the file size after adjustment and repeat finding the root cause and fix it.
June 24, 2011 at 6:55 am
As i have the option to restart the service with new tempdb settings, how about changing the default size of tempdb and create 4 data files in total on 4 different disk but how do i make sure any process uses 1st file and when that file gets full then it has to go to the second file.
June 24, 2011 at 7:13 am
Here is what i found after looking in to the tempdb disk usage report.
Though the rserved space is 1500 gb on a single disk it used only 250 gb and the rest is unused but when a user executes any query using temp tables the size is going over 1500 gb but i was expecting that only when 1500gb is completely used it has to go over tha. Can someone please explain the problem please.
June 24, 2011 at 7:17 am
Also my other question is , does the temp release space to OS after dropping huge temp tables or do we have to shrink or restart the service to reclaim space back.
June 24, 2011 at 12:00 pm
Tara-1044200 (6/24/2011)
Also my other question is , does the temp release space to OS after dropping huge temp tables or do we have to shrink or restart the service to reclaim space back.
tempdb only knows one direction: to expand. If the space is no longer needed, it will be reserved for future use, "assuming" there might be another heavy query like this in the near future. So, to shrink the file just to expand it a few seconds later doesn't make sense to the SQL Server engine (neither does it to me 😉 ).
It is more efficient, to keep the space allocated than to release it and request it again.
Like I said in my previous post: remove the root cause first, then define the appropriate size of tempdb and shrink it to that size.
I don't think the approach to find a different way to manage tempdb should be the major concern right now. The major issue should be to find and eliminate the root cause.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply