Percent of restore database SQL SERVER 2000

  • I need to know which percent of a restore database operation in a certain moment

  • add the stats clause to the restore command, see BOL

    ---------------------------------------------------------------------

  • 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.

  • 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

    ---------------------------------------------------------------------

  • 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:)

  • 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

    ---------------------------------------------------------------------

  • 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!!:)

  • 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

  • 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?

  • Hope sys.dm_exec_requests is dynamic management view featured from SQL 2005 onwards :hehe:

  • You can find it in 2008 as well.

  • You have it on 2008 as well .

  • 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