none
shrink tempdb RRS feed

  • Question

  • is it mandatory to run FREEPROCCACHE before shrinking tempdb?

    if i shrink tempdb without FREEPROCCACHE, what will be the difference, will it take more time to shrink.


    SQL Server DBA

    Monday, June 10, 2019 8:41 AM

Answers

  • is it mandatory to run FREEPROCCACHE before shrinking tempdb?

    if i shrink tempdb without FREEPROCCACHE, what will be the difference, will it take more time to shrink.


    SQL Server DBA

    Normally on a busy system normal shrink would not work, if that works no need to run freeproccache but if you desperately need the space by shrinking and so not want to restart you should run the command and shrink

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, June 10, 2019 9:45 AM
    Moderator
  • I think you should identify what queries eat the space up , and not doing a restart every time 

    How many files the tempdb have?

    https://www.brentozar.com/archive/2016/01/cheat-sheet-how-to-configure-tempdb-for-microsoft-sql-server/

     

    ---to determine the space used by objects in TempDB:

    SELECT
     SPID = s.session_id,
     s.[host_name],
     s.[program_name],
     s.status,
     r.granted_query_memory,
     t.text,
     sourcedb = DB_NAME(r.database_id),
     workdb = DB_NAME(dt.database_id),
     mg.*
    FROM sys.dm_exec_sessions s
    INNER JOIN sys.dm_exec_connections c
    ON s.session_id = c.most_recent_session_id
    LEFT OUTER JOIN sys.dm_exec_requests r
    ON r.session_id = s.session_id
    LEFT OUTER JOIN
    (
      SELECT
       session_id,
       database_id
      FROM
       sys.dm_tran_session_transactions t WITH (NOLOCK)
      INNER JOIN
       sys.dm_tran_database_transactions dt WITH (NOLOCK)
      ON
       t.transaction_id = dt.transaction_id
     WHERE dt.database_id = DB_ID('tempdb')
    GROUP BY
     session_id,
     database_id
    ) dt
    ON s.session_id = dt.session_id
    CROSS APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle, 
    c.most_recent_sql_handle)) t
    LEFT OUTER JOIN sys.dm_exec_query_memory_grants mg
    ON s.session_id = mg.session_id
    WHERE r.database_id = DB_ID('tempdb') OR dt.database_id = DB_ID('tempdb')
    AND R.status = 'running';

    -- This DMV query shows currently executing tasks and 
    -- tempdb space usage
    -- Once you have isolated the task(s) that are generating lots 
    -- of internal object allocations, 
    -- you can even find out which TSQL statement and its query plan 
    -- for detailed analysis



    select top 10
    t1.session_id, 
    t1.request_id, 
    t1.task_alloc,
         t1.task_dealloc,  
        (SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
              (CASE WHEN statement_end_offset = -1 
                  THEN LEN(CONVERT(nvarchar(max),text)) * 2 
                       ELSE statement_end_offset 
                 END - t2.statement_start_offset)/2)
         FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
     (SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle)) as query_plan

    from      (Select session_id, request_id,
    sum(internal_objects_alloc_page_count +   user_objects_alloc_page_count) as task_alloc,
    sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count) as task_dealloc
           from sys.dm_db_task_space_usage 
           group by session_id, request_id) as t1, 
           sys.dm_exec_requests as t2
    where t1.session_id = t2.session_id and 
    (t1.request_id = t2.request_id) and 
          t1.session_id > 50
    order by t1.task_alloc DESC

    -- Queries causing the most (de)allocations in tempDB
    SELECT TOP 10
    tsu.session_id, tsu.request_id, tsu.task_alloc, tsu.task_dealloc,
    erq.command, erq.database_id, DB_NAME(erq.database_id) AS [database_name],
    (SELECT SUBSTRING([text], statement_start_offset/2 + 1,
    (CASE WHEN statement_end_offset = -1
    THEN LEN(CONVERT(nvarchar(max), [text])) * 2
    ELSE statement_end_offset
    END - statement_start_offset) / 2
    )
    FROM sys.dm_exec_sql_text(erq.[sql_handle])) AS query_text,
    qp.query_plan
    FROM
    (SELECT session_id, request_id, 
    SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count) as task_alloc,
    SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count) as task_dealloc
    FROM sys.dm_db_task_space_usage
    GROUP BY session_id, request_id) AS tsu
    INNER JOIN sys.dm_exec_requests AS erq ON tsu.session_id = erq.session_id AND tsu.request_id = erq.request_id
    OUTER APPLY sys.dm_exec_query_plan(erq.[plan_handle]) AS qp
    WHERE tsu.session_id > 50 AND database_id >= 5
    ORDER BY tsu.task_alloc DESC
    GO

    --user sessions that are
     --allocating internal objects, including currently active tasks
    SELECT  
        t1.session_id, 
        (t1.internal_objects_alloc_page_count + task_alloc) as allocated, 
        (t1.internal_objects_dealloc_page_count + task_dealloc) as     
        deallocated  
    from sys.dm_db_session_space_usage as t1,  
        (select session_id,  
            sum(internal_objects_alloc_page_count) 
                as task_alloc, 
        sum (internal_objects_dealloc_page_count) as  
            task_dealloc  
          from sys.dm_db_task_space_usage group by session_id) as t2 
    where t1.session_id = t2.session_id and t1.session_id >50 
    order by allocated DESC


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, June 10, 2019 9:56 AM
    Moderator
  • Hi Zeal DBA,

     

    Compared to shrinking tempdb, I suggest you find out what caused tempdb to grow and eat up space.

     

    You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space that is used in the tempdb files:

     

    -- Determining the Amount of Free Space in tempdb

    SELECT SUM(unallocated_extent_page_count) AS [free pages],

      (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]

    FROM sys.dm_db_file_space_usage;

     

    -- Determining the Amount Space Used by the Version Store

    SELECT SUM(version_store_reserved_page_count) AS [version store pages used],

      (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]

    FROM sys.dm_db_file_space_usage;

     

    -- Determining the Amount of Space Used by Internal Objects

    SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],

      (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]

    FROM sys.dm_db_file_space_usage;

     

    -- Determining the Amount of Space Used by User Objects

    SELECT SUM(user_object_reserved_page_count) AS [user object pages used],

      (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]

    FROM sys.dm_db_file_space_usage;

     

     

     

    You can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views

    to identify large queries, temporary tables, or table variables that are using lots of tempdb disk space:

     

    -- Obtaining the space consumed by internal objects in all currently running tasks in each session

    SELECT session_id,

      SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,

      SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count

    FROM sys.dm_db_task_space_usage

    GROUP BY session_id;

     

    -- Obtaining the space consumed by internal objects in the current session for both running and completed tasks

    SELECT R2.session_id,

      R1.internal_objects_alloc_page_count

      + SUM(R2.internal_objects_alloc_page_count) AS session_internal_objects_alloc_page_count,

      R1.internal_objects_dealloc_page_count

      + SUM(R2.internal_objects_dealloc_page_count) AS session_internal_objects_dealloc_page_count

    FROM sys.dm_db_session_space_usage AS R1

    INNER JOIN sys.dm_db_task_space_usage AS R2 ON R1.session_id = R2.session_id

    GROUP BY R2.session_id, R1.internal_objects_alloc_page_count,

      R1.internal_objects_dealloc_page_count;;

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, June 11, 2019 7:10 AM

All replies

  • Yep, I even think   you would  need to run DROPCLEANBUFFERS  command... What wrong with your tempdb?

    Are you running  out of space?


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, June 10, 2019 9:09 AM
    Moderator
  • yes sometimes it is eating up space, and i shrink it but i think FREEPROCCACHE is not good for performance, so what if i shrink tempdb without FREEPROCCACHE ?

    SQL Server DBA

    Monday, June 10, 2019 9:39 AM
  • is it mandatory to run FREEPROCCACHE before shrinking tempdb?

    if i shrink tempdb without FREEPROCCACHE, what will be the difference, will it take more time to shrink.


    SQL Server DBA

    Normally on a busy system normal shrink would not work, if that works no need to run freeproccache but if you desperately need the space by shrinking and so not want to restart you should run the command and shrink

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, June 10, 2019 9:45 AM
    Moderator
  • I think you should identify what queries eat the space up , and not doing a restart every time 

    How many files the tempdb have?

    https://www.brentozar.com/archive/2016/01/cheat-sheet-how-to-configure-tempdb-for-microsoft-sql-server/

     

    ---to determine the space used by objects in TempDB:

    SELECT
     SPID = s.session_id,
     s.[host_name],
     s.[program_name],
     s.status,
     r.granted_query_memory,
     t.text,
     sourcedb = DB_NAME(r.database_id),
     workdb = DB_NAME(dt.database_id),
     mg.*
    FROM sys.dm_exec_sessions s
    INNER JOIN sys.dm_exec_connections c
    ON s.session_id = c.most_recent_session_id
    LEFT OUTER JOIN sys.dm_exec_requests r
    ON r.session_id = s.session_id
    LEFT OUTER JOIN
    (
      SELECT
       session_id,
       database_id
      FROM
       sys.dm_tran_session_transactions t WITH (NOLOCK)
      INNER JOIN
       sys.dm_tran_database_transactions dt WITH (NOLOCK)
      ON
       t.transaction_id = dt.transaction_id
     WHERE dt.database_id = DB_ID('tempdb')
    GROUP BY
     session_id,
     database_id
    ) dt
    ON s.session_id = dt.session_id
    CROSS APPLY sys.dm_exec_sql_text(COALESCE(r.sql_handle, 
    c.most_recent_sql_handle)) t
    LEFT OUTER JOIN sys.dm_exec_query_memory_grants mg
    ON s.session_id = mg.session_id
    WHERE r.database_id = DB_ID('tempdb') OR dt.database_id = DB_ID('tempdb')
    AND R.status = 'running';

    -- This DMV query shows currently executing tasks and 
    -- tempdb space usage
    -- Once you have isolated the task(s) that are generating lots 
    -- of internal object allocations, 
    -- you can even find out which TSQL statement and its query plan 
    -- for detailed analysis



    select top 10
    t1.session_id, 
    t1.request_id, 
    t1.task_alloc,
         t1.task_dealloc,  
        (SELECT SUBSTRING(text, t2.statement_start_offset/2 + 1,
              (CASE WHEN statement_end_offset = -1 
                  THEN LEN(CONVERT(nvarchar(max),text)) * 2 
                       ELSE statement_end_offset 
                 END - t2.statement_start_offset)/2)
         FROM sys.dm_exec_sql_text(sql_handle)) AS query_text,
     (SELECT query_plan from sys.dm_exec_query_plan(t2.plan_handle)) as query_plan

    from      (Select session_id, request_id,
    sum(internal_objects_alloc_page_count +   user_objects_alloc_page_count) as task_alloc,
    sum (internal_objects_dealloc_page_count + user_objects_dealloc_page_count) as task_dealloc
           from sys.dm_db_task_space_usage 
           group by session_id, request_id) as t1, 
           sys.dm_exec_requests as t2
    where t1.session_id = t2.session_id and 
    (t1.request_id = t2.request_id) and 
          t1.session_id > 50
    order by t1.task_alloc DESC

    -- Queries causing the most (de)allocations in tempDB
    SELECT TOP 10
    tsu.session_id, tsu.request_id, tsu.task_alloc, tsu.task_dealloc,
    erq.command, erq.database_id, DB_NAME(erq.database_id) AS [database_name],
    (SELECT SUBSTRING([text], statement_start_offset/2 + 1,
    (CASE WHEN statement_end_offset = -1
    THEN LEN(CONVERT(nvarchar(max), [text])) * 2
    ELSE statement_end_offset
    END - statement_start_offset) / 2
    )
    FROM sys.dm_exec_sql_text(erq.[sql_handle])) AS query_text,
    qp.query_plan
    FROM
    (SELECT session_id, request_id, 
    SUM(internal_objects_alloc_page_count + user_objects_alloc_page_count) as task_alloc,
    SUM(internal_objects_dealloc_page_count + user_objects_dealloc_page_count) as task_dealloc
    FROM sys.dm_db_task_space_usage
    GROUP BY session_id, request_id) AS tsu
    INNER JOIN sys.dm_exec_requests AS erq ON tsu.session_id = erq.session_id AND tsu.request_id = erq.request_id
    OUTER APPLY sys.dm_exec_query_plan(erq.[plan_handle]) AS qp
    WHERE tsu.session_id > 50 AND database_id >= 5
    ORDER BY tsu.task_alloc DESC
    GO

    --user sessions that are
     --allocating internal objects, including currently active tasks
    SELECT  
        t1.session_id, 
        (t1.internal_objects_alloc_page_count + task_alloc) as allocated, 
        (t1.internal_objects_dealloc_page_count + task_dealloc) as     
        deallocated  
    from sys.dm_db_session_space_usage as t1,  
        (select session_id,  
            sum(internal_objects_alloc_page_count) 
                as task_alloc, 
        sum (internal_objects_dealloc_page_count) as  
            task_dealloc  
          from sys.dm_db_task_space_usage group by session_id) as t2 
    where t1.session_id = t2.session_id and t1.session_id >50 
    order by allocated DESC


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, June 10, 2019 9:56 AM
    Moderator
  • Hi Zeal DBA,

     

    Compared to shrinking tempdb, I suggest you find out what caused tempdb to grow and eat up space.

     

    You can use the sys.dm_db_file_space_usage dynamic management view to monitor the disk space that is used in the tempdb files:

     

    -- Determining the Amount of Free Space in tempdb

    SELECT SUM(unallocated_extent_page_count) AS [free pages],

      (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]

    FROM sys.dm_db_file_space_usage;

     

    -- Determining the Amount Space Used by the Version Store

    SELECT SUM(version_store_reserved_page_count) AS [version store pages used],

      (SUM(version_store_reserved_page_count)*1.0/128) AS [version store space in MB]

    FROM sys.dm_db_file_space_usage;

     

    -- Determining the Amount of Space Used by Internal Objects

    SELECT SUM(internal_object_reserved_page_count) AS [internal object pages used],

      (SUM(internal_object_reserved_page_count)*1.0/128) AS [internal object space in MB]

    FROM sys.dm_db_file_space_usage;

     

    -- Determining the Amount of Space Used by User Objects

    SELECT SUM(user_object_reserved_page_count) AS [user object pages used],

      (SUM(user_object_reserved_page_count)*1.0/128) AS [user object space in MB]

    FROM sys.dm_db_file_space_usage;

     

     

     

    You can use the sys.dm_db_session_space_usage and sys.dm_db_task_space_usage dynamic management views

    to identify large queries, temporary tables, or table variables that are using lots of tempdb disk space:

     

    -- Obtaining the space consumed by internal objects in all currently running tasks in each session

    SELECT session_id,

      SUM(internal_objects_alloc_page_count) AS task_internal_objects_alloc_page_count,

      SUM(internal_objects_dealloc_page_count) AS task_internal_objects_dealloc_page_count

    FROM sys.dm_db_task_space_usage

    GROUP BY session_id;

     

    -- Obtaining the space consumed by internal objects in the current session for both running and completed tasks

    SELECT R2.session_id,

      R1.internal_objects_alloc_page_count

      + SUM(R2.internal_objects_alloc_page_count) AS session_internal_objects_alloc_page_count,

      R1.internal_objects_dealloc_page_count

      + SUM(R2.internal_objects_dealloc_page_count) AS session_internal_objects_dealloc_page_count

    FROM sys.dm_db_session_space_usage AS R1

    INNER JOIN sys.dm_db_task_space_usage AS R2 ON R1.session_id = R2.session_id

    GROUP BY R2.session_id, R1.internal_objects_alloc_page_count,

      R1.internal_objects_dealloc_page_count;;

     

    Hope this could help you .

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, June 11, 2019 7:10 AM