November 4, 2016 at 9:50 am
We restarted a server running SQL Server 2014 and one of the databases came up in a recovery pending state. I looked at the SQL log and found no error message stating why it was recovery pending. I found that strange, then I noticed that usually there is a dbcc checkdb immediately following a database start, this did not exist for this database. I took the database off line, then brought it back on-line and the database was available, and in the log there was a DBCC CHECKDB run as normal. The next reboot of the server was normal, no issues with any of the databases. There are 8 databases on this instance. The only thing different on this reboot was this was the first reboot after TDE was removed from all user databases on the instance.
Anyone have this sort of experience? Management is asking what happened, and I am at a loss. Any help will be appreciated.
Thank you,
Dep
November 4, 2016 at 10:30 am
After removing TDE did you ensure that the decryption process was complete before you restarted the box? What was the size of the actual data in the transaction log at the time of the restart?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
November 4, 2016 at 10:56 am
Did you check the default trace for any hints?
😎
November 4, 2016 at 11:31 am
Yes, I was watching the decryption using this script:
use master;
go
Create table #EncState
(
Encryption_state int,
Description varchar(150)
)
insert into #EncState values (0, 'No database encryption key present, no encryption')
insert into #EncState values (1, 'Unencrypted')
insert into #EncState values (2, 'Encryption in progress')
insert into #EncState values (3, 'Encrypted')
insert into #EncState values (4, 'Key change in progress')
insert into #EncState values (5, 'Decryption in progress')
insert into #EncState values (6, 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)')
select
sd.name, es.Description, dek.set_date, dek.key_algorithm, dek.key_length, dek.percent_complete --, getdate()
from sys.dm_database_encryption_keys dek
join sysdatabases sd on dek.database_id = sd.dbid
join #EncState es on dek.encryption_state = es.Encryption_state
order by dek.percent_complete DESC
drop table #EncState
All Databases had a value of 1, Unencrypted.
I then removed the database encryption key and the server was shutdown/restarted almost 4 hours later.
Transaction log was 14 MB.
November 4, 2016 at 12:08 pm
Not exactly sure what I am looking for, but all I am seeing during that time is Tempdb being created, and several objects being creaetd in tempdb, and lots of missing column Statistics
I see a few Object:Altered when I brought the db online.
but missing column Statistics, Object:Created, Audit Login Failed Login, are the events around the startup.
Thank you,
Dep
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply