Recovery Pending with no Error message in log

  • 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

  • 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

  • Did you check the default trace for any hints?

    😎

  • 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.

  • 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