locked
Cant Reduce the Size of Temp DB in production , though the free space is 95% RRS feed

  • Question

  • Hi,

    In my production environment , the TempDb is consuming around 30 GB, where i can see the free space is around 95%.

    I tried to shrink and end up with no results. I Canot go with Restarting the services, since its production server. So i have to ignore the same.

    I even tried to clear cache, buffer by using below Queries.  but again didnt work out. 

    DBCC FREEPROCCACHE -- clean cache
    DBCC DROPCLEANBUFFERS -- clean buffers
    DBCC FREESYSTEMCACHE ('ALL') -- clean system cache
    DBCC FREESESSIONCACHE -- clean session cache
    DBCC SHRINKDATABASE(tempdb, 10); -- shrink tempdb
    dbcc shrinkfile ('tempdev') -- shrink db file
    dbcc shrinkfile ('templog') -- shrink log file

    I can see around few TempTables alive in tempdb, but those are very very small in size. I am not sure why this is not coming down. Can you please help me to suggest on the same. 

    Thank you 


    hemadri

    Monday, March 16, 2015 12:27 PM

Answers

  • However, unless this is causing an actual problem, shrinking it will just cause it to grow again.  The database is that size because at some point in time, it was required to be that size.

    With today's capacity figures 30GB is not huge, the database use may require it. Consider increasing disk capacity.



    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014





    Monday, March 16, 2015 4:55 PM
  • Hi

    If you can, arrange for a restart of SQL Server; this is the simplest and most reliable method for shrinking tempdb.


    Please click "Mark As Answer" if my post helped. Tony C.

    Hi, as mentioned the only way to shrink tempdb is to restart SQL Server.

    If disk space is an issue then consider moving tempdb to another volume as described here: -

    https://msdn.microsoft.com/en-us/library/ms345408.aspx


    Please click "Mark As Answer" if my post helped. Tony C.

    Monday, March 16, 2015 5:01 PM
  • Monday, March 16, 2015 5:28 PM
  • Hi,

    Leave it as it is 30 G is not a big space utilized if it is not allowing you to shrink means the something is holding the space. Please see what is holding and utilizing using below article

    https://technet.microsoft.com/en-us/library/ms176029(v=sql.105).aspx


    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 Article

    MVP

    Monday, March 16, 2015 5:28 PM
    Answerer
  • Hi 

    You need to identify what objects/data/plans consume disk space

    -- 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

     ---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';


    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

    Tuesday, March 17, 2015 6:51 AM

All replies

  • Hi

    If you can, arrange for a restart of SQL Server; this is the simplest and most reliable method for shrinking tempdb.


    Please click "Mark As Answer" if my post helped. Tony C.

    Monday, March 16, 2015 12:30 PM
  • Why are you shrinking the files? Most probably it is going to grow back again.

    Which file is huge? log file or data file? Try to shrink in small increments and see if it works.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    Monday, March 16, 2015 12:33 PM
  • Please see:

    https://support.microsoft.com/en-us/kb/307487/en-us?wa=wsignin1.0

    Use the SHRINKDATABASE option.

    However, unless this is causing an actual problem, shrinking it will just cause it to grow again.  The database is that size because at some point in time, it was required to be that size.

    Monday, March 16, 2015 1:12 PM
    Answerer
  • However, unless this is causing an actual problem, shrinking it will just cause it to grow again.  The database is that size because at some point in time, it was required to be that size.

    With today's capacity figures 30GB is not huge, the database use may require it. Consider increasing disk capacity.



    Kalman Toth Database & OLAP Architect SQL Server 2014 Database Design
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014





    Monday, March 16, 2015 4:55 PM
  • Hi

    If you can, arrange for a restart of SQL Server; this is the simplest and most reliable method for shrinking tempdb.


    Please click "Mark As Answer" if my post helped. Tony C.

    Hi, as mentioned the only way to shrink tempdb is to restart SQL Server.

    If disk space is an issue then consider moving tempdb to another volume as described here: -

    https://msdn.microsoft.com/en-us/library/ms345408.aspx


    Please click "Mark As Answer" if my post helped. Tony C.

    Monday, March 16, 2015 5:01 PM
  • Monday, March 16, 2015 5:28 PM
  • Hi,

    Leave it as it is 30 G is not a big space utilized if it is not allowing you to shrink means the something is holding the space. Please see what is holding and utilizing using below article

    https://technet.microsoft.com/en-us/library/ms176029(v=sql.105).aspx


    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 Article

    MVP

    Monday, March 16, 2015 5:28 PM
    Answerer
  • Hi 

    You need to identify what objects/data/plans consume disk space

    -- 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

     ---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';


    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

    Tuesday, March 17, 2015 6:51 AM