October 20, 2011 at 3:55 am
Hi All
Just been diagnosing a bit of blocking on live, all resolved which is great.
When running sp_lock I noticed that a couple of spids in tempdb have a resource of [ENCRYPTION_SCAN], strange I thought to myself as we dont use TDE.
As we use a 3rd party for our hosting I thought, ok, they must be putting something in across their environment, so I emailed and asked, and they came back with that they wasn't doing anything, any changes to anything need to be approved by both parties which is in our contract with them.
So I looked through sys.databases and nothing was showing up as enabled for encryption in case one of the dev guys has played with the server (they have SA rights, I know, I know but I only just started in the position a couple of weeks back, and performance has been the main thing to tackle as the company hasn't had a DBA since it started, permissions is on my list of things to sort out), nothing stood out
So I put sp_lock encryption_scan into google and looked at the top post from stackoverflow which gave a query to check to see any certificates have been created and encryption keys, which returned 0 rows
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
So I'm at a bit of a stump to actually know what it is, as everything I look at relates to TDE.
If any one could shine some light on this, it would be very much useful, as while looking this up a couple of other people have had the same thing.
Thanks
Ant
October 20, 2011 at 5:29 am
I think I may have found the "cause" of this. One of the web apps we have uses encryption to store the passwords of registered users into the database.
Unsure if this would be why ENCRYPTION_SCAN is showing in sp_locks as tracing it back it does look to be coming from our web servers.
November 28, 2011 at 3:44 am
Just to add a comment - I have exactly the same issue. Looking at why a query has suddenly stopped returning I found the same thing - a string of [ENCRYPTION_SCAN] type resource select locks on tempdb. No TDE in use, no encrypted DBs, no known use of any form of encryption. If anyone can help... one of the tables in question is compressed. Could that be related?
November 28, 2011 at 3:51 am
simon-811452 (11/28/2011)
Just to add a comment - I have exactly the same issue. Looking at why a query has suddenly stopped returning I found the same thing - a string of [ENCRYPTION_SCAN] type resource select locks on tempdb. No TDE in use, no encrypted DBs, no known use of any form of encryption. If anyone can help... one of the tables in question is compressed. Could that be related?
Please dont hijack peoples posts, if you could start your own thread giving as much detail as you possibly can, DDL of the query, DDL of the objects used in the query, and sample data in a consumable format, we may be able to help you better.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply