September 8, 2011 at 6:55 am
I had a database of 2tb in size which went into recovery mode as the server was rebooted when there was insert statement runnig. I fugred that recovery would take few days to finish and so i deleted the database files and started restoring the database from a backup which is runing since 18 hrs and hwen i check the status now it is in recovery again, why is it so?
September 8, 2011 at 7:03 am
Did you restore with NORECOVERY?
If so just do RESTORE <dbname> WITH RECOVERY and it'll go back online.
Otherwise this may be able to tell if how much loonger you may need to wait :
SELECT r.[session_id]
, c.[client_net_address]
, s.[host_name]
, c.[connect_time]
, [request_start_time] = s.[last_request_start_time]
, [current_time] = CURRENT_TIMESTAMP
, r.[percent_complete]
, [estimated_finish_time] = DATEADD(MILLISECOND, r.[estimated_completion_time], CURRENT_TIMESTAMP)
, [estimated_seconds_left] = DATEDIFF(s, CURRENT_TIMESTAMP, DATEADD(MILLISECOND, r.[estimated_completion_time], CURRENT_TIMESTAMP))
, current_command = SUBSTRING ( t.[text], r.[statement_start_offset]/2, COALESCE(NULLIF(r.[statement_end_offset], -1)/2, 2147483647) )
, module = COALESCE(QUOTENAME(OBJECT_SCHEMA_NAME(t.[objectid], t.[dbid])) + '.' + QUOTENAME(OBJECT_NAME(t.[objectid], t.[dbid])), '<ad hoc>')
FROM sys.dm_exec_requests AS r
INNER JOIN sys.dm_exec_connections AS c
ON r.[session_id] = c.[session_id]
INNER JOIN sys.dm_exec_sessions AS s
ON r.[session_id] = s.[session_id]
CROSS APPLY sys.dm_exec_sql_text(r.[sql_handle]) AS t
WHERE r.[percent_complete] <> 0;
September 8, 2011 at 7:12 am
The qry gives no waiting time it assumes 100% complete but the restore statement issues first is not compelted yet though the database state is "in recovery" instead of "restoring..."
September 8, 2011 at 7:15 am
Darn, it means that your backup also had long running transactions or something else blocking recovery (don't know how to debug this).
September 8, 2011 at 7:20 am
Hmm, is there a way to find when the restore would FINISH.
September 8, 2011 at 7:23 am
Tara-1044200 (9/8/2011)
Hmm, is there a way to find when the restore would FINISH.
The query I posted is supposed to do that. This is the part after the 100% that bugs me to debug... calling in help.
September 8, 2011 at 7:29 am
What is the actual state of the database (status_desc in sys.databases)
There's a major difference between RESTORING and RECOVERING
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2011 at 7:40 am
The state is "RECOVERING".
Say if there was some insert statement runing while i am doing a full backup and the insert was nto complete by the time backup finished, so if i am restoring the same backup does it go into "In Recovery" mode after the restore is finished,does it roll back the insert during the Recovery state or doesit finish the insert?
September 8, 2011 at 7:47 am
No. It'll be restoring until it finished rolling the insert back.
Has the SQL instance restarted? Is the DB set to autoclose?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2011 at 7:57 am
yes, the instance was restarted.
September 8, 2011 at 8:02 am
After the restore?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2011 at 8:13 am
No, it was restarted when there was an active INSERT transaction and so it rolled back when the server was up and because i assumed it takes more time to recover i deleted the database files by stopping the server and then restored the database which is still runnig the restore.
September 8, 2011 at 8:16 am
What happened before the restore is utterly irrelevant.
If the restore was still running the state would be restoring, not recovering. Have a look through the SQL error log, starting with the database restore statement and see what happened.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 9, 2011 at 6:30 am
How do i know the % of recovery completed so far. Looks like i cant see this from the log becasue this recovery is not a regular one but this is during the restore. Ican see the restore status as 100% complete but no way to find recovery state?
September 11, 2011 at 6:38 pm
Check if this one helps..
SELECT A.NAME,B.TOTAL_ELAPSED_TIME/60000 AS [Running Time],
B.ESTIMATED_COMPLETION_TIME/60000 AS [Remaining],
B.PERCENT_COMPLETE as [%],(SELECT TEXT FROM sys.dm_exec_sql_text(B.SQL_HANDLE))AS COMMAND FROM
MASTER..SYSDATABASES A, sys.dm_exec_requests B
WHERE A.DBID=B.DATABASE_ID AND B.COMMAND LIKE '%BACKUP%'
order by percent_complete desc,B.TOTAL_ELAPSED_TIME/60000 desc
SELECT A.NAME,B.TOTAL_ELAPSED_TIME/60000 AS [Running Time],
B.ESTIMATED_COMPLETION_TIME/60000 AS [Remaining],
B.PERCENT_COMPLETE as [%],(SELECT TEXT FROM sys.dm_exec_sql_text(B.SQL_HANDLE))AS COMMAND FROM
MASTER..SYSDATABASES A, sys.dm_exec_requests B
WHERE A.DBID=B.DATABASE_ID AND B.COMMAND LIKE '%RESTORE%'
order by percent_complete desc,B.TOTAL_ELAPSED_TIME/60000 desc
SELECT r.session_id,r.command,CONVERT(NUMERIC(6,2),r.percent_complete)
AS [Percent Complete],CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [ETA Completion Time],
CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [ETA Min],
CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [ETA Hours],
CONVERT(VARCHAR(1000),(SELECT SUBSTRING(text,r.statement_start_offset/2,
CASE WHEN r.statement_end_offset = -1 THEN 1000 ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')
Thank You,
Best Regards,
SQLBuddy
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply