October 14, 2010 at 6:38 am
Some 3rd party app ran a monster select query that took 51Gb of tempdb space in internal objects.
The tempdb disk has only 2 Gb of space left and I would like to shrink it.
When this happens with SQL Server 2005, I wait for a low-activity moment and then run DBCC FREESYSTEMCACHE, but SQL 2000 doesn't have this DBCC command.
How can I free some space from tempdb?
-- Gianluca Sartori
October 14, 2010 at 6:54 am
Doesn’t DBCC FREESYSTEMCACHE clear only the memory structures? Are you sure that it shrinks the tempdb?. I don’t have here SQL Server 2000 to test it, but if I remember correctly there is no problem running DBCC SHRINKFILE on tempDB.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 14, 2010 at 7:02 am
DBCC FREESYSTEMCACHE('ALL') doesn't shrink tempdb, but deallocates all interal objects in tempdb, so that you can shrink it.
Until internal objects are deallocated, you can't shrink tempdb under the size in use by those objects.
Other than restarting the service, I don't see a workaround.
-- Gianluca Sartori
October 14, 2010 at 1:52 pm
What about using DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS?
Joie Andrew
"Since 1982"
October 15, 2010 at 1:12 am
Thanks for the suggestions, but I tried both and they don't seem to dellocate internal objects.
Probably that's the reason why MS decided to ship FREESYSTEMCACHE with SQL2005.
-- Gianluca Sartori
October 15, 2010 at 1:57 am
[EDIT] Removed
-- Gianluca Sartori
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply