Why do we see ENCRYPTION_SCAN locks in a SQL server

Question

I have searched all Microsoft artilces but no one as answered.

Why do we see ENCRYPTION_SCAN locks in a SQL server?

We are frequently getting ENCRYPTION_SCAN in SQL Server 2016 Ent Edition.

No TDE enable. This server contains only one database.

Mainly we can see this in TempDB

Answer 1

Hi @Vijay Kumar ,

There are three types of operations that acquire lock with the resource_type of DATABASE and resource_subtype of ENCRYPTION_SCAN:

• Encryption scan performed during TDE enable/disable
• Bulk Allocations that happen as part of bcp/bulk insert/select-into/index operations, etc
• Sort spills that are done as part of sort operators in the query plan

Suggest you read the blog Why do we see ENCRYPTION_SCAN locks in a SQL server again.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.

Answer 2

Mainly we can see this in TempDB

This may be because Tempdb may still be encrypted. When you enable encryption for user DB tempdb is also encrypted. I am aware of bug where removing user db encryption does not fully removes temdb encryption. The flag is not toggled so it still shows encrypted for tempdb

select is_encrypted, is_master_key_encrypted_by_server from sys.databases where name='tempdb'  
----or   
select is_encrypted, is_master_key_encrypted_by_server from sys.databases   

Does for any of above query you get value 1 for tempdb or user database ?

What is output of select @@version

Restart the sql server and this should go as this will recreate the tempdb, not quite sure though. For more details read why-do-we-see-encryption-scan-locks-in-a-sql-server/

You said you have no TDE enabled so in that case below might cause this, this is also mentioned in the article I have added above.

There are three types of operations that acquire lock with the resource_type of DATABASE and resource_subtype of ENCRYPTION_SCAN :

Encryption scan performed during TDE enable/disable

Bulk Allocations that happen as part of bcp/bulk insert/select-into/index operations, etc

Sort spills that are done as part of sort operators in the query plan

These locks are taken to serialize operations like bulk allocations and sorts with encryption scan.