[ENCRYPTION_SCAN] on the TempDB in SQL 2008

Question

Before anyone says..."did you see the other posts"... yes I did and NONE of them are aswered.

Here is one question that I too am having the same issure.

"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"

This was replied by Stephanie... here is her answer.

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

• Proposed As Answer by Jonathan Kehayias<abbr class="affil">MVP, Moderator</abbr> Monday, April 18, 2011 3:33 AM
• Marked As Answer by Alex Feng - DBA<abbr class="affil">Microsoft Community Contributor, Moderator</abbr> Saturday, April 23, 2011 7:34 AM

As you can see Jonathan adn Alex think this solves the issue... it does not ... our server and not one of the databases on this server have every been encrypted.

I understand the concept... but it does not explain why I am getting [ENCRYPTION_SCAN] resource lock.

Can anyone really answer this question?

Thank you!!

Tim

Answer 1

Post the query you are running to find this information and the exact output and I'll send it to someone on the product team to ask what would be causing it outside of TDE.

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Answer 2

i created this proc to capture the information

-------------------------

[master]

GO

/****** Object: StoredProcedure [dbo].[usp_sp_lock] Script Date: 03/23/2012 09:33:25 ******/

SET

USE

ANSI_NULLS

ON

GO

SET

QUOTED_IDENTIFIER ON

GO

 

ALTER

procedure [dbo].[usp_sp_lock] --- 1996/04/08 00:00

@ServerName

varchar (255) = 'ICS01'

as

set

nocount on

set

transaction isolation level read committed

insert into bireporting.master.dbo.tbl_Lock_log --table in the master database that data is to get /inserted/added to

([ServerName]

,[DateTimeStamp]

,[spid]

,[dbid]

,[ObjID]

,[IndId]

,[type]

,[resource]

,[mode]

,[status])

select

@ServerName

as ServerName

,GETDATE() AS 'datetimestamp'

,convert (smallint, req_spid) As spid

,rsc_dbid As dbid

,rsc_objid As ObjId

,rsc_indid As IndId

,substring (v.name, 1, 4) As Type

,substring (rsc_text, 1, 32) as Resource

,substring (u.name, 1, 8) As Mode

,substring (x.name, 1, 5) As Status

from

master.dbo.syslockinfo,

master.dbo.spt_values v,

master.dbo.spt_values x,

master.dbo.spt_values u

where master.dbo.syslockinfo.rsc_type = v.number

and v.type = 'LR'

and master.dbo.syslockinfo.req_status = x.number

and x.type = 'LS'

and master.dbo.syslockinfo.req_mode + 1 = u.number

and u.type = 'L'

order by spid

 

return

(0) -- sp_lock

select

* from bireporting.master.dbo.tbl_Lock_log----------------------------

Answer 3

here is the results

SELECT

top 10 [id]

,[ServerName]

,[DateTimeStamp]

,[spid]

,[dbid]

,[ObjID]

,[IndId]

,[type]

,[resource]

,[mode]

,[status]

FROM [master].[dbo].[tbl_Lock_log]

where

resource = '[ENCRYPTION_SCAN]'

order

by [DateTimeStamp] desc

Answer 4

id ServerName DateTimeStamp spid dbid ObjID IndId type resource mode status
161304 ICS01 2012-03-23 02:30:41.450 116 2 0 0 DB [ENCRYPTION_SCAN]                S GRANT
161200 ICS01 2012-03-20 02:30:42.510 136 2 0 0 DB [ENCRYPTION_SCAN]                S GRANT
160998 ICS01 2012-03-06 02:31:44.803 57 2 0 0 DB [ENCRYPTION_SCAN]                S GRANT
158110 ICS01 2012-01-20 02:32:45.280 57 2 0 0 DB [ENCRYPTION_SCAN]                S GRANT
158168 ICS01 2012-01-20 02:32:45.280 112 2 0 0 DB [ENCRYPTION_SCAN]                S GRANT
155675 ICS01 2012-01-15 02:32:39.750 110 2 0 0 DB [ENCRYPTION_SCAN]                S GRANT
145778 ICS01 2012-01-02 10:15:24.050 108 2 0 0 DB [ENCRYPTION_SCAN]                S GRANT
145343 ICS01 2012-01-02 10:00:25.337 172 2 0 0 DB [ENCRYPTION_SCAN]                S GRANT
140600 ICS01 2012-01-02 09:45:27.090 117 2 0 0 DB [ENCRYPTION_SCAN]                S GRANT
140184 ICS01 2012-01-01 07:00:07.190 139 2 0 0 DB [ENCRYPTION_SCAN]                S GRANT

Answer 5

if you need more infor please let me know

Thank you

tim

Answer 6

What is the output of DBCC TRACESTATUS on the server?

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Answer 7

no... let me explain...

i have a ssis package that runs several stored procs in sucession. each proc separated by GO in one script. when the step that runs the stored proc fails then the proc above runs to capture the reason... the procs in the ssis package create datasets in tables to be pulled later in the ssis package and sent to a vendor.

the database we pull from does not have much by the way of record locking or testing for it... the reason is it is a outside vendor's database (and application).

so i started capturing the different locks that happen and where there is performace issues to show them. i was having trouble with the failing of my "data creation" step in the ssis package so i put "trigger" on the fail to see what was happening. the result was the result set above.

Tim

Answer 8

I still need to know the output of DBCC TRACESTATUS on the server.  If you aren't going to provide the information that is being requested to try and get an internal product team answer for you about whether this is normal or not, I can only recommend that you contact Product Support and open a support case to have an engineer assigned to troubleshoot the problem with you.  

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Answer 9

btw... the USP_sp_lock is based on the sp_lock

Answer 10

I still need to know the output of DBCC TRACESTATUS on the server.  If you aren't going to provide the information that is being requested to try and get an internal product team answer for you about whether this is normal or not, I can only recommend that you contact Product Support and open a support case to have an engineer assigned to troubleshoot the problem with you.  

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

here is the results you asked for

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

sorry i misread your question

Answer 11

What is the output of SELECT @@VERSION?

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Answer 12

Can you collect the output of sys.dm_exec_requests when the problem occurs and provide that to me by email through my blog?  One of the support engineers I know is looking at where this could occur in the source and needs to know your version from SELECT @@VERSION as well as needs to see the exec_requests output from the problem occurring.  Thanks,

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Answer 13

What is the output of SELECT @@VERSION?

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)

Mar 29 2009 10:11:52

Copyright (c) 1988-2008 Microsoft Corporation

Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

 

(1 row(s) affected)

Answer 14

Hello ,

If you use windows login and if it takes time to authenticate from AD then you might see encryption scan. Troubleshooting this issue may need to collect more data as Jonathan pointed, whch requires a more in-depth level of support.  You can visit the following link to see the various paid support options that are available to better meet your needs. http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

Thank You

Jayaprakash JO - MSFT

Answer 15

Hello ,

If you use windows login and if it takes time to authenticate from AD then you might see encryption scan. Troubleshooting this issue may need to collect more data as Jonathan pointed, whch requires a more in-depth level of support.  You can visit the following link to see the various paid support options that are available to better meet your needs. http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

Thank You

Jayaprakash JO - MSFT

Why would this cause blocking in tempdb for other sessions, and what is the purpose of the ENCRYPTION_SCAN here?

---

Jonathan Kehayias | Principal Consultant, SQLSkills.com
SQL Server MVP | Microsoft Certified Master: SQL Server 2008
Author of Troubleshooting SQL Server: A Guide for Accidental DBAs
Feel free to contact me through My Blog or Twitter. Become a SQLskills Insider!
Please click the Mark as Answer button if a post solves your problem!

Answer 16

Hello,

Are we seeing blocking.

Thank You

Jayaprakash JO - MSFT

Answer 17

Java,

you did not answer the question...Why would this cause blocking in tempdb for other sessions, and what is the purpose of the ENCRYPTION_SCAN here?