SQL Server TDE - how to tell when complete?

Question

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.

score 3 · Accepted Answer · answered Dec 16 '12 at 23:01

3

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.

answered Dec 16 '12 at 23:01

Martin Smith

438,706
87
741
845

Thanks! That was it. I also came across this article for anyone else that needs this information: http://msdn.microsoft.com/en-us/library/bb677274.aspx – Neil Weicher Dec 18 '12 at 16:10
Interesting: percent_complete goes to 100 long before encryption_state changes. So is it complete when it reaches 100 percent, or is it complete when encryption_state changes? – Neil Weicher Dec 18 '12 at 16:13
@Neilw - Not sure what it does in that time interval. I'd assume when encryption_state changes though. – Martin Smith Dec 18 '12 at 21:37
I tried encrypting a 50GB database. It went to 100% complete in about 4 minutes. But encryption_state did not change to 3 until about ten minutes. – Neil Weicher Dec 19 '12 at 22:23

score 0 · Answer 2 · answered Sep 15 '20 at 19:27

old, but I'll just place it over here. pay attention on "percent_complete"

SELECT DB_NAME(database_id) DbName, encryption_state EncryptState, percent_complete, key_algorithm KeyAlgorithm, key_length KeyLength, encryptor_type EncryptType FROM sys.dm_database_encryption_keys;

SQL Server TDE - how to tell when complete?

2 Answers2