What to do IN RECOVEY and lock database in the same time does not allow to be ONLINE

  • Dear Colleagues,

    I have moved  a  Database (1.5T) to a new server, same version (2k8r2), detached and attached, ok.

    After a  few  days  I restarted the instance,  now  my database is in IN RECOVERY state, in the error log I could see:
    Recovery of database 'dbalejandria_stagingarea' (5) is 8% complete (approximately 17 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.

    and in the Management Studio database IN RECOVERY  during almost  24 hours, I'm afraid that is stuck,
    after reading  this tip, for control my own anxiety

    http://jasonbrimhall.info/2015/05/18/database-in-recovery-update/

    Now, I have passed to the worried state, after observe that there is no advance in the situation

    Database X  2018-04-20 15:35:47.777    2018-04-19 17:34:43.470    384    Recovery MetaData     8%    0,283333333333333    CHECKPOINT    0    0    NULL    Transaction is Active

    Database X 2018-04-20 15:35:47.777    NULL    1858    NULL   
      8%    0,283333333333333    CHECKPOINT    0    0    NULL    NULL

    Also I have checked sys.dm_exec_requests I could see two sessions:

    session_id    request_id    start_time    status    command    sql_handle    statement_start_offset    statement_end_offset    plan_handle 
       database_id    user_id    connection_id    blocking_session_id    wait_type    wait_time    last_wait_type    wait_resource    open_transaction_count    
    open_resultset_count    transaction_id    context_info    percent_complete    estimated_completion_time    cpu_time    total_elapsed_time
        scheduler_id    task_address    reads    writes    logical_reads    text_size    language    date_format    date_first    quoted_identifier    arithabort   
     ansi_null_dflt_on    ansi_defaults    ansi_warnings    ansi_padding    ansi_nulls    concat_null_yields_null    transaction_isolation_level    lock_timeout    
    deadlock_priority    row_count    prev_error    nest_level    granted_query_memory    executing_managed_code    group_id    query_hash   
     query_plan_hash (sorry these are the column names)

    7    0    1900-01-01 00:00:00.000    background    DB STARTUP    NULL    NULL    NULL    NULL    1    1    NULL    0    SLEEP_DBSTARTUP    88    SLEEP_DBSTARTUP        0    1    0    NULL    0    0
        46    81051588    1    0x0000000006012988    558    124    2935    4096    us_english    mdy    7    0    0    0    0    0    0    0    0    2    -1    0    0    0  
      1    0    0    1    NULL    NULL

    14    0    1900-01-01 00:00:00.000    background    DB STARTUP    NULL    NULL    NULL    NULL    0    1    NULL    -3    LCK_M_S    81045798    LCK_M_S    KEY: 5:196608 (2d188ef96974)    0    1    0    NULL    8    932043145    452    81048034    0    0x0000000006008BC8    21914
        1933    586856    4096    us_english    mdy    7    0    0    0    0    0    0    0    0    2    -1    0    0    0    1    0    0    1    NULL  
      NULL

    It appears like a lock, and I'm thinking that killing session 14 perhaps would help me.

    I'm looking for options that help me to bring ONLINE again my database, I'm thinking in restart my Instance, drop the database, and attach again, but as a last resort option,
    What do you think ??

    Thanks  for your answers

  • luismarinaray - Friday, April 20, 2018 2:15 PM

    7    0    1900-01-01 00:00:00.000    background    DB STARTUP    NULL    NULL    NULL    NULL    1    1    NULL    0    SLEEP_DBSTARTUP    88    SLEEP_DBSTARTUP        0    1    0    NULL    0    0
        46    81051588    1    0x0000000006012988    558    124    2935    4096    us_english    mdy    7    0    0    0    0    0    0    0    0    2    -1    0    0    0  
      1    0    0    1    NULL    NULL

    14    0    1900-01-01 00:00:00.000    background    DB STARTUP    NULL    NULL    NULL    NULL    0    1    NULL    -3    LCK_M_S    81045798    LCK_M_S    KEY: 5:196608 (2d188ef96974)    0    1    0    NULL    8    932043145    452    81048034    0    0x0000000006008BC8    21914
        1933    586856    4096    us_english    mdy    7    0    0    0    0    0    0    0    0    2    -1    0    0    0    1    0    0    1    NULL  
      NULL

    It appears like a lock, and I'm thinking that killing session 14 perhaps would help me.

    I'm looking for options that help me to bring ONLINE again my database, I'm thinking in restart my Instance, drop the database, and attach again, but as a last resort option,
    What do you think ??

    Thanks  for your answers

    Those two system processes are normal when starting up a database - sleep_dbstartup just checks the status intermittently. I wouldn't kill them at this point. Try the following for queries to check the status of the recovery:
    Tracking database recovery progress using information from DMV

    Sue

  • Sue,

    Thanks for your response. Great documentation.
    After a couple of hours, watching,
    database_transaction_log_bytes_reserved from sys.dm_tran_database_transactions, for both transactions

    0
    3639948
    and it remains at the same value.
    database_transaction_next_undo_lsn
    NULL
    NULL

    What  else can I do? More than 24 hours  IN RECOVERY state, no  ERRORLOG updates about this

    Best  Regards

  • For future reference,
    After  waiting  3+ days waiting to finish the recovery  state, I have to:
    1.) Locate a last and good backup
    2.)  Restart SQL Server using trace flag /t3608
    3.)  Delete  phsyical files
    4.) Restart SQL Server normally
    5.) Restore my database
    Finally it opens up, probable origin of the problem high VLF count
    Best  Regards

  • luismarinaray - Monday, April 23, 2018 8:45 AM

    For future reference,
    After  waiting  3+ days waiting to finish the recovery  state, I have to:
    1.) Locate a last and good backup
    2.)  Restart SQL Server using trace flag /t3608
    3.)  Delete  phsyical files
    4.) Restart SQL Server normally
    5.) Restore my database
    Finally it opens up, probable origin of the problem high VLF count
    Best  Regards

    Wow...3+ days. Did you check the number of VLFs you have now? And growth increment?

    Sue

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply