Path: csiph.com!x330-a1.tempe.blueboxinc.net!usenet.pasdenom.info!weretis.net!feeder4.news.weretis.net!eternal-september.org!feeder.eternal-september.org!.POSTED!not-for-mail From: Erland Sommarskog Newsgroups: comp.databases.ms-sqlserver,microsoft.public.sqlserver.programming Subject: Re: SQL Server Database encryption_state still says 2 for a 110GB Database for the past one week Followup-To: comp.databases.ms-sqlserver Date: Tue, 26 Jul 2011 14:38:08 +0200 Organization: Erland Sommarskog Lines: 31 Message-ID: References: <9c759c9d-2b86-4058-b91f-5f438e6d70fd@hd10g2000vbb.googlegroups.com> Mime-Version: 1.0 Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: 8bit Injection-Info: mx04.eternal-september.org; posting-host="DD6dU+BfJNjsjSP4/K/V7w"; logging-data="5701"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+wrMTjB3NEGhZqvs9BPJNq" User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32) Cancel-Lock: sha1:XAvDYxz/cY4WuGZK3K+WQWVSM7w= Xref: x330-a1.tempe.blueboxinc.net comp.databases.ms-sqlserver:571 Query Builder (querybuilder@gmail.com) writes: > I implemented TDE on one of my SQL Server instance. When I query the > database state with the following query it returns 2 to one of my > databases which is about 110GB. Its been 2 weeks and its still says > 2. > > SELECT db_name(database_id), encryption_state,* > FROM sys.dm_database_encryption_keys >... > I would greatly appreciate if anyone can shred some light on this and > confirm that the encryption is complete. How do I confirm that the > encryption is complete? Well, if it says 2, that would indicate that it is not complete. What does the other columns in this DMV report for this database? Particularly, what does percent_complete report? I found http://www.simple-talk.com/sql/database-administration/transparent-data- encryption/ which suggests some alternate ways to verify completion. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx