none
Dose AlwaysOn cause high version_store usage?

    Question

  • Hi All,

    This is a rare issue.

    1. Firstly got report tempdb is full:

    Version store is full. New version(s) could not be added. A transaction that needs to access the version store may be rolled back. Please refer to BOL on how to configure tempdb for versioning.

    2. Can see all are nearly full in 8 temp db data files each is 15GB

    free pages Total TempDB SizeInGB Free TempDB SpaceInGB unallocated_extent_page_count user_object_reserved_page_count version_store_GB internal_object_reserved_page_count mixed_extent_page_count
    1920 14.9999999385600 0.0146484374400 1920 96 14.9734496457000 8 1456
    1920 14.9999999385600 0.0146484374400 1920 112 14.9741210324160 32 1328
    1928 14.9999999385600 0.0147094725960 1928 64 14.9743041378840 40 1336
    1936 14.9999999385600 0.0147705077520 1936 224 14.9713134152400 0 1600
    1952 14.9999999385600 0.0148925780640 1952 24 14.9754027706920 24 1224
    1952 14.9999999385600 0.0148925780640 1952 32 14.9729003292960 16 1552
    1952 14.9999999385600 0.0148925780640 1952 72 14.9724730832040 8 1576
    1952 14.9999999385600 0.0148925780640 1952 136 14.9735717160120 8 1368

    3.select * from sys.dm_tran_active_snapshot_database_transactions

    transaction_id transaction_sequence_num commit_sequence_num session_id is_snapshot first_snapshot_sequence_num max_version_chain_traversed average_version_chain_traversed elapsed_time_seconds
    0 7140034 NULL 66 0 NULL 0 0 226684
    0 7140089 NULL 66 0 NULL 0 0 226683
    0 7140117 NULL 66 0 NULL 0 0 226682
    0 7140118 NULL 66 0 NULL 0 0 226682
    0 7140120 NULL 66 0 NULL 0 0 226677
    0 7140121 NULL 66 0 NULL 0 0 226677
    0 7140122 NULL 66 0 NULL 0 0 226677
    0 7140123 NULL 66 0 NULL 0 0 226677
    0 7140124 NULL 66 0 NULL 0 0 226677
    0 7140130 NULL 66 0 NULL 0 0 226672

    I can see the esapsed time seconds is 226684, that is about 62.96 hours!

    4.

    select CONVERT (varchar(30), getdate(), 121) AS runtime,a.*,b.kpid,b.blocked,b.lastwaittype,b.waitresource,b.dbid,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

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   

    runtime transaction_id transaction_sequence_num commit_sequence_num session_id is_snapshot first_snapshot_sequence_num max_version_chain_traversed average_version_chain_traversed elapsed_time_seconds kpid blocked lastwaittype waitresource dbid cpu physical_io memusage login_time last_batch open_tran status hostname program_name cmd loginame request_id
    2017-03-21 15:06:55.210 0 7140034 NULL 66 0 NULL 0 0 226774 6956 0 HADR_WORK_QUEUE                 1 0 0 0 2017-03-21 15:05:05.903 2017-03-21 15:05:05.903 0 background                     UNKNOWN TOKEN   sa                                                                                                                               0
    2017-03-21 15:06:55.210 0 7140089 NULL 66 0 NULL 0 0 226773 6956 0 HADR_WORK_QUEUE                 1 0 0 0 2017-03-21 15:05:05.903 2017-03-21 15:05:05.903 0 background                     UNKNOWN TOKEN   sa                                                                                                                               0
    2017-03-21 15:06:55.210 0 7140117 NULL 66 0 NULL 0 0 226772 6956 0 HADR_WORK_QUEUE                 1 0 0 0 2017-03-21 15:05:05.903 2017-03-21 15:05:05.903 0 background                     UNKNOWN TOKEN   sa                                                                                                                               0
    2017-03-21 15:06:55.210 0 7140118 NULL 66 0 NULL 0 0 226772 6956 0 HADR_WORK_QUEUE                 1 0 0 0 2017-03-21 15:05:05.903 2017-03-21 15:05:05.903 0 background                     UNKNOWN TOKEN   sa                                                                                                                               0
    2017-03-21 15:06:55.210 0 7140120 NULL 66 0 NULL 0 0 226767 6956 0 HADR_WORK_QUEUE                 1 0 0 0 2017-03-21 15:05:05.903 2017-03-21 15:05:05.903 0 background                     UNKNOWN TOKEN   sa                                                                                                                               0
    2017-03-21 15:06:55.210 0 7140121 NULL 66 0 NULL 0 0 226767 6956 0 HADR_WORK_QUEUE                 1 0 0 0 2017-03-21 15:05:05.903 2017-03-21 15:05:05.903 0 background                     UNKNOWN TOKEN   sa                                                                                                                               0
    2017-03-21 15:06:55.210 0 7140122 NULL 66 0 NULL 0 0 226767 6956 0 HADR_WORK_QUEUE                 1 0 0 0 2017-03-21 15:05:05.903 2017-03-21 15:05:05.903 0 background                     UNKNOWN TOKEN   sa                                                                                                                               0
    2017-03-21 15:06:55.210 0 7140123 NULL 66 0 NULL 0 0 226767 6956 0 HADR_WORK_QUEUE                 1 0 0 0 2017-03-21 15:05:05.903 2017-03-21 15:05:05.903 0 background                     UNKNOWN TOKEN   sa                                                                                                                               0
    2017-03-21 15:06:55.210 0 7140124 NULL 66 0 NULL 0 0 226767 6956 0 HADR_WORK_QUEUE                 1 0 0 0 2017-03-21 15:05:05.903 2017-03-21 15:05:05.903 0 background                     UNKNOWN TOKEN   sa                                                                                                                               0
    2017-03-21 15:06:55.210 0 7140130 NULL 66 0 NULL 0 0 226762 6956 0 HADR_WORK_QUEUE                 1 0 0 0 2017-03-21 15:05:05.903 2017-03-21 15:05:05.903 0 background                     UNKNOWN TOKEN   sa                                                                                                                               0

    I can see the commit_sequence_num is null, lastwaittype is "HADR_WORK_QUEUE", status is background, cmd is unknown token, loginname is sa, login_time is "2017-03-21 15:05:05.903" which is not 62 hours ago.

    5.select * from sys.dm_exec_sessions where session_id = 66

    session_id login_time host_name program_name host_process_id client_version client_interface_name security_id login_name nt_domain nt_user_name status context_info cpu_time memory_usage total_scheduled_time total_elapsed_time endpoint_id last_request_start_time last_request_end_time reads writes logical_reads is_user_process text_size language date_format date_first quoted_identifier arithabort ansi_null_dflt_on ansi_defaults ansi_warnings ansi_padding ansi_nulls concat_null_yields_null transaction_isolation_level lock_timeout deadlock_priority row_count prev_error original_security_id original_login_name last_successful_logon last_unsuccessful_logon unsuccessful_logons group_id database_id authenticating_database_id open_transaction_count
    66 2017-03-21 15:05:05.903 NULL NULL NULL NULL NULL 0x01 sa NULL NULL sleeping 0 0 0 0 0 2017-03-21 15:05:05.903 NULL 0 0 0 0 4096 us_english mdy 7 0 0 0 0 0 0 0 0 2 -1 0 0 0 0x01 sa NULL NULL NULL 1 1 NULL 0

    status is sleep

    We have Server A/B/C, this is server C which runs async replica.

    Microsoft SQL Server 2014 (SP2-CU4) (KB4010394) - 12.0.5540.0 (X64) 
    Jan 27 2017 03:40:25 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

    When firstly troubleshooting this issue, I notice the session id 66 is running from system account and I could kill it. after a couple of times, now I could not kill the session:

    Only user processes can be killed.

    tempdb is still full, cannot release any version store due to this magic transaction, does anyone what is happening here?

    Thanks,

    Albert

    Tuesday, March 21, 2017 4:18 AM

All replies

  • 1. I checked server A actually has 800GB temp DB files, Server C only has 120GB, but I don't think this is the problem.

    2. Also the server C was upgraded to a newer patch last week, other servers are due to upgrade this week.

    Server Name (No column name)
    server_c\instance1 Microsoft SQL Server 2014 (SP2-CU4) (KB4010394) - 12.0.5540.0 (X64) 
    Jan 27 2017 03:40:25 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

    server_b\instance1 Microsoft SQL Server 2014 (SP2-CU3) (KB3204388) - 12.0.5538.0 (X64) 
    Dec 15 2016 17:52:48 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

    server_a\instance1 Microsoft SQL Server 2014 (SP2-CU3) (KB3204388) - 12.0.5538.0 (X64) 
    Dec 15 2016 17:52:48 
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

    Tuesday, March 21, 2017 4:29 AM
  • What is the frequency of your log backup jobs?
    Tuesday, March 21, 2017 5:07 AM
  • every 15 minutes.
    Tuesday, March 21, 2017 5:21 AM
  • There are 2 large databases on that instance, one (1.9TB) is out of sync.

    another one (2.7TB) has 24 hours estimated recovery time.

    I am trying to remove them from AG and apply log backups then join them back.

    This is second time in the last 3 weeks now...

     
    Tuesday, March 21, 2017 5:25 AM
  • after the database(2.7TB) is removed, tempdb is released.

    I am applying the log backups now. see how it goes later

    Wednesday, March 22, 2017 12:13 AM
  • Always on does not use the version store, it does use snapshot isolation on the secondaries.
    Wednesday, March 22, 2017 1:30 AM
  • Hilary is correct, but I should elaborate on his answer.

    While AlwaysOn itself does not use the version store, readable secondary replicas use Snapshot Isolation which is remapped from the normal Read Committed. Snapshot Isolation *DOES* use the version store, thus if you have readable secondary replicas you will absolutely use the local version store which resides in TempDB on each replica.

    -Sean


    The views, opinions, and posts do not reflect those of my company and are solely my own. No warranty, service, or results are expressed or implied.

    Saturday, March 25, 2017 2:56 PM
    Answerer