Andy DBA - Thursday, May 25, 2017 2:23 PM

I wrote a similar script and I used a wait loop to display encryption progress every 2 seconds.  I found it helpful when encrypting large databases so I can tell that the server is not hung up and also get a feel for about when it will be done.  Fair warning: For very large databases it may say "100% complete..." for a few seconds before it finally finishes.

To display progress, the following code can be pasted between the "Exec (@sql)"  and "FETCH NEXT FROM apply_tde into @dbname" commands in Prem Kumar's script:

/* Show encryption progress */
Declare @PercentDone as int
Declare @EncState as int = 0
Declare @StatusMsg as nvarchar(255)

WHILE @EncState not in (3,-1)
BEGIN
    SELECT @EncState = coalesce (max(encryption_state), -1), @PercentDone = coalesce (max(percent_complete),0)
    FROM sys.dm_database_encryption_keys
    WHERE db_name(database_id) = @dbname

    set @StatusMsg =
   N'Encrypting ' +
   @dbname +
   ' database using AZ_TDE_PRD certificate. ' +
   cast(@PercentDone as nvarchar(4)) +
   N'%% complete...'

    RAISERROR( @StatusMsg, -1, -1) WITH NOWAIT;
    WAITFOR DELAY '00:00:02'
END
IF @EncState = -1 RAISERROR( N'No encryption key found!', -1, -1) WITH NOWAIT;
Reply premkumardr - Yes, I agree this will help in fetch the right status when the database is getting encrypted