Post reply

TempDB keeps growing for no reason.

  • October 10, 2018 at 8:54 am

    #374770

    Hi All,

    Thank you for looking into my question.

    I have SQL Server 2017 Enterprise server FailOver cluster (No Shared storage though) with Always On availability groups in production.  
    The Failover cluster has 3 nodes.  Node 1 hosts 3 availability groups and acts as a primary replica for all three availability groups.  Node 2 acts as a synchronous secondary readable replica for all three availability groups.  Node 3 is in a different data center and acts as a asynchronous replica for DR purpose. 

    The tempdb data file in Node 2 keeps growing.  Node 1(Primary replica) and Node 3 are doing fine.  I am not able to shrink the temp db file, there is no space available to shrink.  I tried DBCC DROPCLEANBUFFERS,  FREEPROCCACHE AND FREESYSTEMCACHE on Node 2 and tried to shrink. Still no luck. 

    I understand that shrinking is not a good option. But I am not able to figure out why only one server has this issue and the others are working as expected. 

    Has anyone seen this issue before.  Please help.

    Thanks

  • October 10, 2018 at 9:35 am

    #2008869

    One reason could be Version Store in tempdb. Did you verify what is taking up space in tempdb? Version Store or Internal Objects or User objects?

  • October 10, 2018 at 9:58 am

    #2008876

    Yes. It is version store.   
    Unfortunately, I am just exposed to VersionStore in tempdb. 

    The following query helped me.
    select getdate() AS runtime, SUM (user_object_reserved_page_count)*8 as usr_obj_kb,
    SUM (internal_object_reserved_page_count)*8 as internal_obj_kb,
    SUM (version_store_reserved_page_count)*8 as version_store_kb,
    SUM (unallocated_extent_page_count)*8 as freespace_kb,
    SUM (mixed_extent_page_count)*8 as mixedextent_kb
    FROM sys.dm_db_file_space_usage

    I couldn't find which spid to kill.  Do you have any idea?

  • October 10, 2018 at 10:09 am

    #2008878

    I have solved the issue.  Here is a good article that helped me. 

    https://thesqldude.com/tag/version-store/

  • October 10, 2018 at 10:15 am

    #2008880

    Thank you so much Srikanth for giving me the right direction...! I learnt something new today. 

    Thank you SQL Server central for providing the platform...!

  • October 10, 2018 at 10:50 am

    #2008891

    Glad I could help.

  • October 11, 2018 at 7:14 am

    #2009091

    You can track tempdb usage by three DMVs command:

    The first two will allow you to track allocations at a query & session level. The third tracks allocations across version store, user and internal objects.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply