sql-server-2008encryptiontde

SQL Server TDE - how to tell when complete?

Is there a way to tell when a database is completely encrypted when using SQL 2008 TDE? I.e.,

ALTER DATABASE mydb SET ENCRYPTION ON

The only way I can think of is to keep trying to detach mydb. It won't detach until all pages are encrypted. However, I'd like to have a cleaner way that does not involve detaching?

Thanks.

Solution

  • Looks like this information can be found in sys.dm_database_encryption_keys.

    encryption_state = 3 indicates the database and logs are encrypted. encryption_state = 2 means Encryption in progress in which case you might want to also look at the percent_complete column.