TDE progress monitoring RRS feed

  • Question

  • Here is my situation. I have two database instance , Inst1 has the DB1 and Inst2 has DB2 which is standby/read_only, because it restore log from DB1.

    I enable TDE on DB1, and then backup and restore the log to DB2, then both DB should be encrypted .

    I am using the following script to monitor the TDE percentage progress

    SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,
    encryption_state_desc =
    CASE encryption_state
             WHEN '0'  THEN  'No database encryption key present, no encryption'
             WHEN '1'  THEN  'Unencrypted'
             WHEN '2'  THEN  'Encryption in progress'
             WHEN '3'  THEN  'Encrypted'
             WHEN '4'  THEN  'Key change in progress'
             WHEN '5'  THEN  'Decryption in progress'
             WHEN '6'  THEN  'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)'
             ELSE 'No Status'
    percent_complete,encryptor_thumbprint, encryptor_type  FROM sys.dm_database_encryption_keys

    It works perfectly when doing TDE on DB1 . while I would like to do the same monitoring when restoring Log to DB2,

    the percentage keep at 0. After the Log restore complete. and it show the DB2 is encrypted already.

    How to monitor the TDE percentage for DB2 in this case ?

    Wednesday, August 21, 2019 11:10 AM

All replies

  • anyone has the experience ?

    Wednesday, August 21, 2019 7:31 PM
  • Are you also enabled tde on db2?

    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

    Thursday, August 22, 2019 8:30 AM
  • yes, after the restore log completed.
    Thursday, August 22, 2019 9:53 AM
  • anyone has idea ?
    Friday, August 23, 2019 2:35 AM
  • If I understand you setup, the log files that is transferred is under TDE already so there is nothing to encrypt and thus no progress to monitor other that the redo-log latency


    • Proposed as answer by MF47 Thursday, September 12, 2019 7:28 AM
    Monday, September 9, 2019 8:28 AM
  • I can add that the TDE progress on instance 2 is 100 % if you tried to restore it with recovery provided it is in sync


    Monday, September 9, 2019 8:52 AM
  • yes...

    means after 100%,, it will pending for TDE complete.

    Tuesday, September 10, 2019 2:51 AM
  • my point is that if you restore the secondary with recovery it would be under TDE completely since the primary is under TDE.

    The zero in progress means that nothing is going on. If you pause the TDE at encryption 50 % this state 2 with 0 % until you resume


    Tuesday, September 10, 2019 8:55 AM