none
AG Secondary Tempdb growing hugely RRS feed

  • Question

  • Hi,

    We configured the Alwayson on 3 servers. A -primary(Synchronous),B-Secondary-(Async-Readable),C-Secondary(Asyn-Not readable).Fail over mode is Manual.
    from 2 days the secondary server tempdb is growing hugely. When we checked the Primary server the tempdb is fine.Not growing.
    i am not able to understand why secondary server tempdb is growing hugely. Yesterday night we restarted the Secondary SQL Server ,then also the tempdb didnt became normal. So we increased the space in tempdb drive. By morning the disk got high space alert. Today morning again i restarted the sql server. then tempdb became normal. But still around 55gb is occupied. when i checked the primary server tempdb space . It is also showing around 50 gb.
    I am performed shrinking on primary tempdb but no effect.
    The all databases are in sync. all together dbs size is around 1.7 TB. The maximum i/o operations are goingon only one db which is 1.2 tb.
    As of now i restarted the SQL in secondary so the tempdb became normal.
    But i want to know why the tempdb is around 50gb in primary and secondary?
    How to trouble shoot tempdb in both servers?
    please note that primary server tempdb is not growing. but secondary server tempdb is growing hugely. why is this? now the primary & secondary tempdb size is 50 gb. How to reduce?

    (History: 3 days back the primary server completely crashed.. We forced fail over to secondary. then seccondary became primary and all operatins performed on seconadary as it is primary. After A server came up all dbs we restored & Sync in A server and made that again as primary. Now as usual A server is primary and B server is secondary. after this change the tempdb in secondary server was growing hugely)

    Please help step wise what kind of trouble shoot i can perform on both primary and secondary tempdbs.

    Thanks,
    Jo


    pols

    Wednesday, July 17, 2019 5:14 AM

Answers

  • May i know on which database(Master or tempdb or 123db) i have to execute your recent long query?

    You can run that query in any database, as it looks at the entire server.

    However, I am not sure how relevant the information is. I discussed this in my other post, but I did not make the underlying point clear: your task as a DBA is to make sure that the business has the resources they need to run their work. So if a server runs short on tempdb, the correct resolution is in many cases to increase the tempdb size, and if needed get the required disk space. But if you can conclude that the tempdb growth is due to a new database, maybe that database should be moved elsewhere.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by joo123 Tuesday, July 23, 2019 11:40 AM
    Sunday, July 21, 2019 10:54 AM
  • I just wanted to make sure that this database (which is created only on the secondary server) is the REASON for growth the tempdb.

    If so, see if you are able to optimize the queried ( Sort operator may consume resources, missing indexes and etc)

    Is that possible to move that db to another server?


    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

    • Marked as answer by joo123 Tuesday, July 23, 2019 11:40 AM
    Sunday, July 21, 2019 11:02 AM
    Answerer

All replies

  • https://blogs.msdn.microsoft.com/docast/2018/08/04/rapid-growth-of-tempdb-on-alwayson-secondary-replica-due-to-version-store/

    Your  Secondary replica is setup with Readable there is 14 byte overhead on every row inserted/updated on the primary database. Along with that SQL Server maintains versions of the rows in tempdb database and maps the isolation level to SNAPSHOT ISOLATION level on secondary replica. The row version data is not generated by the primary databases. Instead, the secondary databases generate the row versions.

    select GETDATE() AS runtime,a.*,b.kpid,b.blocked,b.lastwaittype,b.waitresource,db_name(b.dbid) asdatabase_name,
    b.cpu,b.physical_io,b.memusage,b.login_time,b.last_batch,b.open_tran,b.status,b.hostname,
    b.program_name,b.cmd,b.loginame,request_id
    from sys.dm_tran_active_snapshot_database_transactions a
    inner join sys.sysprocesses b
    on a.session_id = b.spid


    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

    Wednesday, July 17, 2019 5:35 AM
    Answerer
  • Hi,

    Thanks for your reply. 

    I already tried this query. In the output around 14 rows are coming. I am not sure what to read and what needs to be done with those session ids.

    As per the article check for is_snap=0 

    But in my output for all the session ids the is_snapshot =1 

    What i should i do?


    pols


    • Edited by joo123 Wednesday, July 17, 2019 5:44 AM
    Wednesday, July 17, 2019 5:42 AM
  • Please read the link I posted above,  you can identify the process running by specific sessionid

    Is that possible to  disable readable secondary option as a workaround for the secondary replica even for a while to see if  tempdb stopped growing?


    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

    Wednesday, July 17, 2019 5:46 AM
    Answerer
  • I can not disable readble secondary. many users are connecting to read the data from dynamics. :( 

    As of now i restarted the sql in seconadary so Tempdb became normal. I need to wait for some time to see whether tempdb is gowing again or not in secondary. Now the tampdb size is around 50 gb in both primary and secondary. . I tried shrinking tempdb in primary but not effecting much. 

    Actaully ths 50 gb is fine or i need work on primary tempdb to get the space normal?

    And one more observation, the users are reading data from many days. Secondary tempdb didnt grown any time much. why this happend now suddenly? 

    Do i need to troubleshoot tempdb in both servers?

    generally what else i can check to trouble shoot tempdb?

    Thanks,



    • Edited by joo123 Wednesday, July 17, 2019 6:05 AM
    Wednesday, July 17, 2019 5:55 AM
  • <<Do i need to troubleshoot tempdb in both servers?

    Yep, how many files does the tempdb on both sites have? Is it located on separated disks from other databases?


    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

    Wednesday, July 17, 2019 6:52 AM
    Answerer
  • Priamary Tempdb: 1-primary data,7 secondary data file ,1-log 

    Secondary Tempdb: 1-primary data,7 secondary data file ,1-log 

    yes Tempdb is configured in separate disk in both servers.


    pols

    Wednesday, July 17, 2019 10:22 AM
  • If you are not able to change the readable secondary option to FALSE , then options available are:
    1. Ensuring that there are no long running update transactions happening on primary.
    2. Adequately sizing the tempdb on the secondary replica.

    Kindly refer the article which has additional details on capacity planning considerations when readable secondary is enabled: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups?view=sql-server-2017

    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

    Wednesday, July 17, 2019 10:25 AM
    Answerer
  • Again tempdb in secondary is growing hugely. I restarted the SQL Server. Then came back to normal 

    when i checked the SQL ogs, i found this message. 

    Message
    Insufficient space in tempdb to hold row versions.  Need to shrink the version store to free up some space in tempdb. Transaction (id=173979 xsn=66734 spid=70 elapsed_time=39881) has been marked as victim and it will be rolled back if it accesses the version store. If the problem persists, the likely cause is improperly sized tempdb or long running transactions. Please refer to BOL on how to configure tempdb for versioning.

    Do i need to check long running queries in primary server? The tempdb we configured in seperate disk with 7 data files and 1 log file. What else i need to check. please guide. 


    pols

    Thursday, July 18, 2019 8:13 AM
  • Again  your temdb keeps growing because row versions that it need to maintain to hod readable secondary database.  

    Ensuring that there are no long running update transactions happening on primary.

    Also I would add additional disk space to temdb as well.

    Kindly refer the article which has additional details on capacity planning considerations when readable secondary is enabled: https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups?view=sql-server-2017


    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

    Thursday, July 18, 2019 9:01 AM
    Answerer
  • Hi joo123,

    >> Do i need to check long running queries in primary server? 

    You can try to use below T-SQL to find Longest Running Query in SQL server.

    SELECT TOP 10
    t.TEXT QueryName,
    s.execution_count AS ExecutionCount,
    s.max_elapsed_time AS MaxElapsedTime,
    ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
    s.creation_time AS LogCreatedOn,
    ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
    ,query_plan
    FROM sys.dm_exec_query_stats s
    CROSS APPLY sys.dm_exec_query_plan( s.plan_handle ) u
    CROSS APPLY sys.dm_exec_sql_text( s.plan_handle ) t
    ORDER BY MaxElapsedTime DESC

    Please refer to How to Find Longest Running Query With Execution Plan.

    Hope this could help you.

    Best regards,
    Cathy Ji 


    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

    Thursday, July 18, 2019 10:18 AM
  • Hi,

    Still the issue persist. I have to restart the SQL Server every time..:(

    I tried checking long running qusries with execution plan using above query 


    I found that all are select and insert queries in one perticular db.

    This DB is created only in seconadary server. Not finding in primary server.

    Actually the secondary server is for read perpose.

    i am not sure who created this db and users are working on this db.

    Also this db is not part of Ag setup.

    Will this could be the reason for tempdb growth?

    Incase after realising if we can restore this db in Primary server add it to Ag then also same issue wil be their right?

    Instead of secondary server and primary server temp db will grow now..

    What should i do?



    pols




    • Edited by joo123 Sunday, July 21, 2019 8:44 AM
    Sunday, July 21, 2019 8:42 AM
  • >>>Will this could be the reason for tempdb growth?

    Sure it could....Is the output db that DB?

    SELECT
    st.dbid AS QueryExecutionContextDBID,
    DB_NAME(st.dbidAS QueryExecContextDBNAME,
    st.objectid AS ModuleObjectId,
    SUBSTRING(st.TEXT,
    dmv_er.statement_start_offset/1,
    (
    CASE WHEN dmv_er.statement_end_offset -1
    THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2
    ELSE dmv_er.statement_end_offset
    END dmv_er.statement_start_offset)/2AS Query_Text,
    dmv_tsu.session_id ,
    dmv_tsu.request_id,
    dmv_tsu.exec_context_id,
    (
    dmv_tsu.user_objects_alloc_page_count dmv_tsu.user_objects_dealloc_page_countAS OutStanding_user_objects_page_counts,
    (
    dmv_tsu.internal_objects_alloc_page_count dmv_tsu.internal_objects_dealloc_page_countAS OutStanding_internal_objects_page_counts,
    dmv_er.start_time,
    dmv_er.command,
    dmv_er.open_transaction_count,
    dmv_er.percent_complete,
    dmv_er.estimated_completion_time,
    dmv_er.cpu_time,
    dmv_er.total_elapsed_time,
    dmv_er.reads,dmv_er.writes,
    dmv_er.logical_reads,
    dmv_er.granted_query_memory,
    dmv_es.HOST_NAME,
    dmv_es.login_name,
    dmv_es.program_name
    FROM sys.dm_db_task_space_usage dmv_tsu
    INNER JOIN sys.dm_exec_requests dmv_er
    ON (dmv_tsu.session_id dmv_er.session_id AND dmv_tsu.request_id dmv_er.request_id)
    INNER JOIN sys.dm_exec_sessions dmv_es
    ON (dmv_tsu.session_id dmv_es.session_id)
    CROSS 
    APPLY sys.dm_exec_sql_text(dmv_er.sql_handlest
    WHERE (dmv_tsu.internal_objects_alloc_page_count dmv_tsu.user_objects_alloc_page_count) > 0
    ORDER BY (dmv_tsu.user_objects_alloc_page_count dmv_tsu.user_objects_dealloc_page_count) + (dmv_tsu.internal_objects_alloc_page_count dmv_tsu.internal_objects_dealloc_page_countDESC


    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

    Sunday, July 21, 2019 8:45 AM
    Answerer
  • Hi,

    I didn't understand your statement (Is the output db that DB?)

    I found that one db which is not part of Ag and it is only in Secondary server. (Example :123db)

    Actually i ran the below query in secondary tempdb ?Did i executed correct?

    SELECT TOP 10
    t.TEXT QueryName,
    s.execution_count AS ExecutionCount,
    s.max_elapsed_time AS MaxElapsedTime,
    ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,
    s.creation_time AS LogCreatedOn,
    ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSec
    ,query_plan
    FROM sys.dm_exec_query_stats s
    CROSS APPLY sys.dm_exec_query_plan( s.plan_handle ) u
    CROSS APPLY sys.dm_exec_sql_text( s.plan_handle ) t
    ORDER BY MaxElapsedTime DESC

    In the top 10 queries i observed that one db (123db)is using in most of the select and insert queries. 

    May i know on which database(Master or tempdb or 123db) i have to execute your recent posted query?

    Also  i googled (https://www.brentozar.com/archive/2011/10/how-tell-if-tempdb-performance-problem/) that if i enable Instant file initialization then it will be useful for tempdb performance. Shall i enable it? 

    But 1st please clarify on which db i need to execute your below query?

    SELECT
    st.dbid AS QueryExecutionContextDBID,
    DB_NAME(st.dbidAS QueryExecContextDBNAME,
    st.objectid AS ModuleObjectId,
    SUBSTRING(st.TEXT,
    dmv_er.statement_start_offset/1,
    (
    CASE WHEN dmv_er.statement_end_offset -1
    THEN LEN(CONVERT(NVARCHAR(MAX),st.TEXT)) * 2
    ELSE dmv_er.statement_end_offset
    END dmv_er.statement_start_offset)/2AS Query_Text,
    dmv_tsu.session_id ,
    dmv_tsu.request_id,
    dmv_tsu.exec_context_id,
    (
    dmv_tsu.user_objects_alloc_page_count dmv_tsu.user_objects_dealloc_page_countAS OutStanding_user_objects_page_counts,
    (
    dmv_tsu.internal_objects_alloc_page_count dmv_tsu.internal_objects_dealloc_page_countAS OutStanding_internal_objects_page_counts,
    dmv_er.start_time,
    dmv_er.command,
    dmv_er.open_transaction_count,
    dmv_er.percent_complete,
    dmv_er.estimated_completion_time,
    dmv_er.cpu_time,
    dmv_er.total_elapsed_time,
    dmv_er.reads,dmv_er.writes,
    dmv_er.logical_reads,
    dmv_er.granted_query_memory,
    dmv_es.HOST_NAME,
    dmv_es.login_name,
    dmv_es.program_name
    FROM sys.dm_db_task_space_usage dmv_tsu
    INNER JOIN sys.dm_exec_requests dmv_er
    ON (dmv_tsu.session_id dmv_er.session_id AND dmv_tsu.request_id dmv_er.request_id)
    INNER JOIN sys.dm_exec_sessions dmv_es
    ON (dmv_tsu.session_id dmv_es.session_id)
    CROSS 
    APPLY sys.dm_exec_sql_text(dmv_er.sql_handlest
    WHERE (dmv_tsu.internal_objects_alloc_page_count dmv_tsu.user_objects_alloc_page_count) > 0
    ORDER BY (dmv_tsu.user_objects_alloc_page_count dmv_tsu.user_objects_dealloc_page_count) + (dmv_tsu.internal_objects_alloc_page_count dmv_tsu.internal_objects_dealloc_page_countDESC

    Thanks,


    pols



    • Edited by joo123 Sunday, July 21, 2019 9:46 AM
    Sunday, July 21, 2019 9:41 AM
  • So you have found a suspect in a database which is not part of the AG?

    Well, to test the hypothesis, fail over the AG, so that the current secondary becomes the primary. If the tempdb still keeps growing on this same server, I guess that we can conclude the non-AG database is the culprit.

    But then comes the next question: should the database be there? Well, if it is a junk database that no one uses, it would be safe to drop it. But if the version store for this database causes tempdb to grow, it is being used, apparently. So one can suspect the database fulfils a need.

    Assuming that this is a case, I can see two solutions:
    1) Size tempdb to fit your workload, and if there is not enough disk space for it, get that disk space.
    2) Move this database to a different instance which has more space for tempdb.

    As for whether this database should be part of the AG, this is something you will need to discuss with the stakeholders for this database. What requirements do they have for avialability and disaster recovery of this database?


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, July 21, 2019 9:53 AM
  • May i know on which database(Master or tempdb or 123db) i have to execute your recent long query?

    You can run that query in any database, as it looks at the entire server.

    However, I am not sure how relevant the information is. I discussed this in my other post, but I did not make the underlying point clear: your task as a DBA is to make sure that the business has the resources they need to run their work. So if a server runs short on tempdb, the correct resolution is in many cases to increase the tempdb size, and if needed get the required disk space. But if you can conclude that the tempdb growth is due to a new database, maybe that database should be moved elsewhere.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by joo123 Tuesday, July 23, 2019 11:40 AM
    Sunday, July 21, 2019 10:54 AM
  • I just wanted to make sure that this database (which is created only on the secondary server) is the REASON for growth the tempdb.

    If so, see if you are able to optimize the queried ( Sort operator may consume resources, missing indexes and etc)

    Is that possible to move that db to another server?


    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

    • Marked as answer by joo123 Tuesday, July 23, 2019 11:40 AM
    Sunday, July 21, 2019 11:02 AM
    Answerer
  • Hello Sir,

    Still the situation is same, I am restarting the sql every time. Below commands worked little bit i am able to shrink the tempdb to maximum extent. 

    use tempdb

    go

    DBCC FREEPROCCACHE WITH NO_INFOMSGS

    dbcc dropcleanbuffers

    dbcc freesystemcache('all')

    dbcc freesessioncache

    dbcc shrinkdatabase(tempdb,10)

    go

    DBCC SHRINKFILE(N'tempdev', 1024, TRUNCATEONLY)

    go

    But after couple of hours again the same thing its growing. I checked the version store value. It also increasing. I googled. some one said -review the redo queue and determine if that is backed up.I am not sure how to review the redo queue and how to determine if that us backedup.

    Please help in this regard.

    Thanks,

    Jo


    pols


    • Edited by joo123 Wednesday, July 24, 2019 12:06 PM
    Wednesday, July 24, 2019 12:02 PM
  • It is compeltely pointless to shrink tempdb. There is no surprise that it will grow again. We have already concluded that your current workload requires this much tempdb.

    Have you failed over the AG so that the primary is now on the database where tempdb is growing? Is tempdb still growing on the same server?

    You need to know which database that causes tempdb growth, so you can make your capacity planning accordingly.

    While it certainly can be possible to track down exactly what in these databases that causes tempdb to grow, that is also a whole lot more work. Adding more disk space is a lot easier, or possibly moving that other database elsewhere.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, July 24, 2019 9:23 PM