ENCRYPTION_SCAN on SQL 2008 R2

Question

Hi all,

I am witnessing a wait resource subtype of 'encryption_scan' on tempdb for processes that use temporary tables or table variables on a new installation of SQL 2008 R2. I checked the tempdb as well as the database for TDE - it is disabled on both.

I would really appreciate if anybody can help understand the problem or confirm it is normal on 2008R2.

TIA,

Nirupam

Answer 1

Looks like some of databases enabled for TDE

Please check these DMVs

sys.databases (Transact-SQL)

sys.certificates (Transact-SQL)

sys.dm_database_encryption_keys (Transact-SQL)

 

for more please visit here http://msdn.microsoft.com/en-us/library/bb934049.aspx

 

 

---

http://uk.linkedin.com/in/ramjaddu

Answer 2

Hi Ram,

The following queries returned no records for tempdb.

 

select * from sys.databases where is_encrypted = 1

select * from sys.certificates

select * from sys.dm_database_encryption_keys

Answer 3

Hi Nirupam,

The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE. This might have a performance effect for unencrypted databases on the same instance of SQL Server.
Try the following query which will return information about the encryption state of a database:

SELECT DB_NAME(e.database_id) AS DatabaseName, 
		e.database_id, 
		e.encryption_state, 
CASE e.encryption_state 
		WHEN 0 THEN 'No key present - encryption unavailable' 
		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' 
END AS encryption_state_desc, 
		c.name, 
		e.percent_complete 
FROM sys.dm_database_encryption_keys AS e 
LEFT JOIN master.sys.certificates AS c 
ON e.encryptor_thumbprint = c.thumbprint

---

Best Regards,
Stephanie Lv

Answer 4

Hi Ram,

The following queries returned no records for tempdb.

 

select * from sys.databases where is_encrypted = 1

select * from sys.certificates

select * from sys.dm_database_encryption_keys

What about other databases on the instance.  You shouldn't be looking specifically at tempdb, you should be looking at any user database on the instance.  If one database on the instance has TDE enabled, them tempdb has to be encrytped.  This is documented in the books online:

http://msdn.microsoft.com/en-us/library/bb934049.aspx

---

Jonathan Kehayias | Senior Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Feel free to contact me through My Blog or Twitter
Please click the Mark as Answer button if a post solves your problem!

Answer 5

I have the same issue.  I checked the instance to see i any db has encryption turned on, and there were none.

 

I tried restarting the server and even dropping the tempdb files, so that it can be re-created.  Still, when i issue sp_lock, i see the 'encryption_scan' on tempdb.  Any other ideas?

thanks in advance.

Answer 6

Stephanie,

i have the same issue as the others here. i ran your script and it returned zero records. I have never had encryption on this server. do you have any other ideas other than just runing this script?

Tim