July 1, 2008 at 11:16 am
I need to know which percent of a restore database operation in a certain moment
July 1, 2008 at 11:46 am
add the stats clause to the restore command, see BOL
---------------------------------------------------------------------
July 11, 2008 at 1:51 pm
yes, Add the stats clause in the RESTORE command and check the DBCC OUTPUTBUFFER(spid) of the specific restore spid, you can see the percentage of completion.
Thanks
Jay
http://www.sqldbops.com
July 12, 2008 at 6:50 am
Jayakumar Krishnan (7/11/2008)
yes, Add the stats clause in the RESTORE command and check the DBCC OUTPUTBUFFER(spid) of the specific restore spid, you can see the percentage of completion.
top tip
---------------------------------------------------------------------
July 20, 2008 at 2:17 am
Processed 1768 pages for database 'DB', file 'System_Data' on file 1.
Processed 85000 pages for database 'DB', file 'DB_Data' on file 1.
Processed 15272 pages for database 'DB', file 'DB_Index' on file 1.
Processed 2 pages for database 'DB', file 'DB_Log_1' on file 1.
RESTORE DATABASE successfully processed 102042 pages in 330.641 seconds (2.528 MB/sec).
I just had a restore done for a database, sample result shown above:
What does the above commands signify? Anyone step by step ?
thanks:)
July 20, 2008 at 5:06 am
rinu philip (7/20/2008)
Processed 1768 pages for database 'DB', file 'System_Data' on file 1.Processed 85000 pages for database 'DB', file 'DB_Data' on file 1.
Processed 15272 pages for database 'DB', file 'DB_Index' on file 1.
Processed 2 pages for database 'DB', file 'DB_Log_1' on file 1.
RESTORE DATABASE successfully processed 102042 pages in 330.641 seconds (2.528 MB/sec).
it means what it says on the tin.
the database being restored consists of 4 files, 3 data and 1 log
there were 1768 8K pages in system_data restored
there were 85000 8K pages in DB_data restored
there were 15272 8K pages in DB_Index restored
the 'recovery' part of the restore (reading the log to make the db consistent by rolling back uncommited transactions) processed 2 pages,
and it took 330 seconds.
thats 2.528 mb\sec which should equate to :
(102042 * 8192)\1024(kb)\1024(mb)\330.641
---------------------------------------------------------------------
July 20, 2008 at 5:59 am
george sibbald (7/20/2008)
rinu philip (7/20/2008)
Processed 1768 pages for database 'DB', file 'System_Data' on file 1.Processed 85000 pages for database 'DB', file 'DB_Data' on file 1.
Processed 15272 pages for database 'DB', file 'DB_Index' on file 1.
Processed 2 pages for database 'DB', file 'DB_Log_1' on file 1.
RESTORE DATABASE successfully processed 102042 pages in 330.641 seconds (2.528 MB/sec).
it means what it says on the tin.
the database being restored consists of 4 files, 3 data and 1 log
there were 1768 8K pages in system_data restored
there were 85000 8K pages in DB_data restored
there were 15272 8K pages in DB_Index restored
the 'recovery' part of the restore (reading the log to make the db consistent by rolling back uncommited transactions) processed 2 pages,
and it took 330 seconds.
thats 2.528 mb\sec which should equate to :
(102042 * 8192)\1024(kb)\1024(mb)\330.641
thanks alot George!!:)
August 5, 2008 at 5:42 am
Hi there ,
you can try something like this :
select
d.name,
percent_complete, dateadd(second,estimated_completion_time/1000, getdate()), Getdate() as now,
datediff(minute, start_time, getdate()) as running, estimated_completion_time/1000/60 as togo,
start_time, command
from sys.dm_exec_requests req
inner join sys.sysdatabases d on d.dbid = req.database_id
where
req.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')
Cheers,
R
August 5, 2008 at 10:35 pm
r.dragoi (8/5/2008)
Hi there ,you can try something like this :
select
d.name,
percent_complete, dateadd(second,estimated_completion_time/1000, getdate()), Getdate() as now,
datediff(minute, start_time, getdate()) as running, estimated_completion_time/1000/60 as togo,
start_time, command
from sys.dm_exec_requests req
inner join sys.sysdatabases d on d.dbid = req.database_id
where
req.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')
Cheers,
R
Will this work for sql server 2000?
August 5, 2008 at 11:01 pm
Hope sys.dm_exec_requests is dynamic management view featured from SQL 2005 onwards :hehe:
Thanks
Jay
http://www.sqldbops.com
August 6, 2008 at 1:38 am
You can find it in 2008 as well.
August 6, 2008 at 3:59 am
You have it on 2008 as well .
February 12, 2010 at 9:44 am
This script works with 2005 and 2008
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')
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply