SQL Server Performance

how to stop tempdb usage temporarily on a live server

Discussion in 'ALL SQL SERVER QUESTIONS' started by Trev256b, Aug 11, 2012.

  1. Trev256b Member

    how can i temporarily stop tempdb usage on alive server, so i can shrink the tempdb files, and not restart the sql server service? tempdb is used a lot so i need a secure way of pausing and removing all connections to tempdb. thanks :)
  2. Shehap MVP, MCTS, MCITP SQL 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


    Kindly let me know if any further help is needed

Share This Page