April 20, 2018 at 2:15 pm
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
April 20, 2018 at 2:36 pm
luismarinaray - Friday, April 20, 2018 2:15 PM7 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 NULL14 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
NULLIt 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
April 20, 2018 at 5:31 pm
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
April 23, 2018 at 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
April 23, 2018 at 10:07 am
luismarinaray - Monday, April 23, 2018 8:45 AMFor 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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply