how to stop tempdb usage temporarily on a live server

In order to control TempDB size, you have to know about the 3 portion of TempDB and usage of each of them

· Version Store

· Internal objects

· User objects

Then you to do what is possible to reduce the size of each portion , but to identify where is the majority of size , you can run the below query:

Select

SUM(user_object_reserved_page_count)*8 asuser_objects_kb,

SUM(internal_object_reserved_page_count)*8 asinternal_objects_kb,

SUM(version_store_reserved_page_count)*8 asversion_store_kb,

SUM(unallocated_extent_page_count)*8 asfreespace_kb

Fromsys.dm_db_file_space_usage

Wheredatabase_id= 2

Therefore ,you have to control activities /solutions hitting each portion such as :

· Version store part : it is used by Read committed snapshot isolation using row versioning, Index rebuild at TempDB and Data change tracking solution,service broker….etc

· User objects part : it is used by table variables or tables returned by functions

· Internal objects part: used by Sort operations, work files resulted by Hash join, LOB large objects such as large table variables or large temp tables…etc

Therefore nothing can be done to reduce TempDB size except stopping or stopping these relevant activities to be able to shrink TempDB size in case of storage bottleneck issue only is exists there..

Despite of that , I don't recommend to shrink TempDB since it impacts adversely on TempDB performance , but it is recommended to allocate it within an isolated drive with enough storage and identify an enough initial size according to

Your workload

You can learn more at http://msdn.microsoft.com/en-us/library/ms345368(v=sql.105).aspx

Kindly let me know if any further help is needed