May 30, 2012 at 10:21 pm
First of all, I want to state that I'm an 'accidental DBA'. So expect there to be large gaps in my knowledge
So this isn't the first time this has happened - our DB server ran out of disk space during a large transaction and out applications started poping up 'transaction log full' error messages. So we kill the query containing that transaction, and it doesn't finish rolling back for hours.
So, my coworker reboots the machine. It goes down, comes up, and the database does into recovery.
Unlike last time, the recovery seemed to stall. It hit about 23%, then the time remaining starts counting UP. So, after another reboot I say I'll keep an eye on it. I haven't even checked the logs until seven hours later - I figured it would be recovered and I could start the app up.
Well, it's in recovery. Only now I can't find the % complete being updated in the logs on a regular basis as usual. And now I'm worried.
So is this a symptom of some big problem? Should I just be patient?
The only thing I can think of to do now is to run something like this:
SELECT state_desc DatabaseStatus_sysDatabase,*
FROM sys.databases where name='Monitor'
which gives me this:
DatabaseStatus_sysDatabase name database_id source_database_id owner_sid create_date compatibility_level collation_name user_access user_access_desc is_read_only is_auto_close_on is_auto_shrink_on state state_desc is_in_standby is_cleanly_shutdown is_supplemental_logging_enabled snapshot_isolation_state snapshot_isolation_state_desc is_read_committed_snapshot_on recovery_model recovery_model_desc page_verify_option page_verify_option_desc is_auto_create_stats_on is_auto_update_stats_on is_auto_update_stats_async_on is_ansi_null_default_on is_ansi_nulls_on is_ansi_padding_on is_ansi_warnings_on is_arithabort_on is_concat_null_yields_null_on is_numeric_roundabort_on is_quoted_identifier_on is_recursive_triggers_on is_cursor_close_on_commit_on is_local_cursor_default is_fulltext_enabled is_trustworthy_on is_db_chaining_on is_parameterization_forced is_master_key_encrypted_by_server is_published is_subscribed is_merge_published is_distributor is_sync_with_backup service_broker_guid is_broker_enabled log_reuse_wait log_reuse_wait_desc is_date_correlation_on is_cdc_enabled is_encrypted is_honor_broker_priority_on
------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- ------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------------------------------------------ ------------ ---------------- ----------------- ----- ------------------------------------------------------------ ------------- ------------------- ------------------------------- ------------------------ ------------------------------------------------------------ ----------------------------- -------------- ------------------------------------------------------------ ------------------ ------------------------------------------------------------ ----------------------- ----------------------- ----------------------------- ----------------------- ---------------- ------------------ ------------------- ---------------- ----------------------------- ------------------------ ----------------------- ------------------------ ---------------------------- ----------------------- ------------------- ----------------- ----------------- -------------------------- --------------------------------- ------------ ------------- ------------------ -------------- ------------------- ------------------------------------ ----------------- -------------- ------------------------------------------------------------ ---------------------- -------------- ------------ ---------------------------
RECOVERING Monitor 7 NULL 0x01050000000000051500000001A35969AE6D0129464DCF52F4010000 2009-08-14 22:37:10.313 80 SQL_Latin1_General_CP1_CI_AS 0 MULTI_USER 0 0 0 2 RECOVERING 0 0 0 0 OFF 0 3 SIMPLE 0 NONE 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 836340DA-E88C-46EA-9FD1-516E51325D11 1 0 NOTHING 0 0 0 0
(1 row(s) affected)
but no ETA.
Any ideas or suggestions?
Ok, off to bed. Going to set my alarm for 4 AM so I can get up and see if anything's changed :unsure:
EDITING to say:
I found this in an article:
select * from sys.dm_exec_requests
I ran it but see no entried for a database_id of 7, which is the database that shows 'In recovery'. Does that mean something is messed up?
Do I have any course of action aside from waiting?
May 31, 2012 at 1:11 am
It will stay in recovery until it has rolled back, rolled forward what it needs to do. Now depending how big the transactions and how long the transactions where running for it could be a long time.
What I would say is you shouldn't of rebooted the server the second time as it will just start the recovery process from scratch, so you could of been 90% through the process, then rebooted and it starts back at 0%.
Now, do you have a backup of the database in question? Can you afford to lose any data which has not been backed up in your recovery model?
May 31, 2012 at 4:31 am
You need to wait.
Long running transactions that fill the log need to be rewound, which can take longer than the original transaction. Rebooting doesn't help.
In the mean time, I'd suggest investigating the transactions that are filling the log and finding a way to break those down into smaller pieces to avoid this issue.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 31, 2012 at 7:55 pm
Ok. Say if the data present in the db is of little to no present value, but we have a great need to get out application up and running so we can finish out our week.
Is there a (quick) way to bring up an 'empty' copy of the db? It's a processing db and we need to get this week's payment completed, so we need this thing up even if we have to loose all of our old data
May 31, 2012 at 9:39 pm
Stop the SQL Server service or disable the service and reboot the server. Delete all the mdf, ldf and ndf files for the database in question. Start SQL Server and restore the last known good backup of the database using the WITH REPLACE option.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply