September 21, 2011 at 12:09 am
I am performing a full restore of a database through Veritas tool on a totally new database .
Now , i am not able to check the status of the restore as i have some prob with the veritas tool < not getting refreshed >
When i try to check the status from the ssms , it is not giving results as the new database is not showing in the drop down and i can't hit queries like :
SELECT command,
s.text,
start_time,
percent_complete,
CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
+ CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
+ CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
+ CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
+ CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')
As it leaves me with no results .
Pls suggest .
TIA \M/
September 21, 2011 at 12:16 am
try using the following:
use master
go
SELECT
percent_complete AS ‘PctComplete’,
start_time AS ‘StartTime’,
command AS ‘Command’,
b.name AS ‘DatabaseName’,
DATEADD(ms,estimated_completion_time,GETDATE()) AS ‘EstimatedEndTime’,
(estimated_completion_time/1000/60) AS ‘EstimatedMinutesToEnd’
FROM sys.dm_exec_requests a
INNER JOIN sys.databases b ON a.database_id = b.database_id
WHERE command like ‘%restore%’
AND estimated_completion_time > 0
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
September 21, 2011 at 5:23 am
And what does this return as well?
Anything weird in the logs?
SELECT state_desc FROM sys.databases WHERE name = <your db name here>
September 21, 2011 at 8:53 am
Henrico Bekker (9/21/2011)
try using the following:
use mastergo
SELECT
percent_complete AS ‘PctComplete’,
start_time AS ‘StartTime’,
command AS ‘Command’,
b.name AS ‘DatabaseName’,
DATEADD(ms,estimated_completion_time,GETDATE()) AS ‘EstimatedEndTime’,
(estimated_completion_time/1000/60) AS ‘EstimatedMinutesToEnd’
FROM sys.dm_exec_requests a
INNER JOIN sys.databases b ON a.database_id = b.database_id
WHERE command like ‘%restore%’
AND estimated_completion_time > 0
Careful with trusting this... "estimated", is not to be trusted, especially the "end time " and "minutes to end"... trust this just about as much as your trust windows to tell you how much time is left copying a file... it's nearly always wrong.
September 21, 2011 at 9:00 am
NJ-DBA (9/21/2011)
Henrico Bekker (9/21/2011)
try using the following:
use mastergo
SELECT
percent_complete AS ‘PctComplete’,
start_time AS ‘StartTime’,
command AS ‘Command’,
b.name AS ‘DatabaseName’,
DATEADD(ms,estimated_completion_time,GETDATE()) AS ‘EstimatedEndTime’,
(estimated_completion_time/1000/60) AS ‘EstimatedMinutesToEnd’
FROM sys.dm_exec_requests a
INNER JOIN sys.databases b ON a.database_id = b.database_id
WHERE command like ‘%restore%’
AND estimated_completion_time > 0
Careful with trusting this... "estimated", is not to be trusted, especially the "end time " and "minutes to end"... trust this just about as much as your trust windows to tell you how much time is left copying a file... it's nearly always wrong.
Really? I've never seen it be off by more than a few seconds.
September 21, 2011 at 9:08 am
Ninja's_RGR'us (9/21/2011)
NJ-DBA (9/21/2011)
Henrico Bekker (9/21/2011)
try using the following:
use mastergo
SELECT
percent_complete AS ‘PctComplete’,
start_time AS ‘StartTime’,
command AS ‘Command’,
b.name AS ‘DatabaseName’,
DATEADD(ms,estimated_completion_time,GETDATE()) AS ‘EstimatedEndTime’,
(estimated_completion_time/1000/60) AS ‘EstimatedMinutesToEnd’
FROM sys.dm_exec_requests a
INNER JOIN sys.databases b ON a.database_id = b.database_id
WHERE command like ‘%restore%’
AND estimated_completion_time > 0
Careful with trusting this... "estimated", is not to be trusted, especially the "end time " and "minutes to end"... trust this just about as much as your trust windows to tell you how much time is left copying a file... it's nearly always wrong.
Really? I've never seen it be off by more than a few seconds.
REALLY? on medium sized databases (100GB+), I've seen it off by minutes.. on huge dbs, even more... especially using third party apps like netbackup...
September 21, 2011 at 9:09 am
NJ-DBA (9/21/2011)
Ninja's_RGR'us (9/21/2011)
NJ-DBA (9/21/2011)
Henrico Bekker (9/21/2011)
try using the following:
use mastergo
SELECT
percent_complete AS ‘PctComplete’,
start_time AS ‘StartTime’,
command AS ‘Command’,
b.name AS ‘DatabaseName’,
DATEADD(ms,estimated_completion_time,GETDATE()) AS ‘EstimatedEndTime’,
(estimated_completion_time/1000/60) AS ‘EstimatedMinutesToEnd’
FROM sys.dm_exec_requests a
INNER JOIN sys.databases b ON a.database_id = b.database_id
WHERE command like ‘%restore%’
AND estimated_completion_time > 0
Careful with trusting this... "estimated", is not to be trusted, especially the "end time " and "minutes to end"... trust this just about as much as your trust windows to tell you how much time is left copying a file... it's nearly always wrong.
Really? I've never seen it be off by more than a few seconds.
REALLY? on medium sized databases (100GB+), I've seen it off by minutes.. on huge dbs, even more... especially using third party apps like netbackup...
I've also seen the end time change dramatically as the restore is occurring.. basically counting down-- if I remember correctly... this was a restore using netbackup from disk of something like 800GB. started out estimating hours longer than it acutally took.
September 21, 2011 at 9:12 am
Of course this can change. It's an estimate and if there's contention the end time will change.
There are also start & end process that can screw this up (create the files, 0 out the log file, running recovery).
No amount of guessing can correctly guess at that without a lot of work.
September 21, 2011 at 9:21 am
Ninja's_RGR'us (9/21/2011)
Of course this can change. It's an estimate and if there's contention the end time will change.There are also start & end process that can screw this up (create the files, 0 out the log file, running recovery).
No amount of guessing can correctly guess at that without a lot of work.
yeah, exactly... I basically use this script to see that there is progress happening and get a guess based on the percentage complete, but I tend to see restore accellerate in the last 50% for some reason. So I think of the end time as the "worst case end time"... it's usually faster in my experience. There are exceptions of course.
September 22, 2011 at 4:05 am
use master
go
SELECT
percent_complete AS 'PctComplete',
start_time AS 'StartTime',
command AS 'Command',
b.name AS 'DatabaseName',
DATEADD(ms,estimated_completion_time,GETDATE()) AS 'EstimatedEndTime',
(estimated_completion_time/1000/60) AS 'EstimatedMinutesToEnd'
FROM sys.dm_exec_requests a
INNER JOIN sys.databases b ON a.database_id = b.database_id
WHERE command like '%restore%'
AND estimated_completion_time > 0
leaves me with no result ....:(
September 22, 2011 at 4:19 am
If you query sys.databases, is the database in question listed?
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply