March 1, 2009 at 8:58 am
I'm using Redgate for backup & restore, and have a long running SQL Agent Job that runs a restore script generated from Redgate. Is there any way to tell how far along the restore is ?? Some system table or log entry that might show how much has been processed ?
Thoughts ?
March 1, 2009 at 6:11 pm
There is a PERFMON counter "Device Throughput Bytes/sec" that you might be able to use to back into the restore time, but otherwise I haven't heard of anything.
March 1, 2009 at 10:59 pm
Is there any option to use STATS keyword in redgate restore command like SQL has.
--Monitoring Options in Restore Command
| STATS [ = percentage ]
MJ
March 1, 2009 at 11:24 pm
I think this snip should help you.
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
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
March 2, 2009 at 5:24 pm
Adiga (3/1/2009)
I think this snip should help you.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
Nice ! I don't see dm_exec_sql_text in BOL. Is it another of their secret undocumented procedures ?
March 2, 2009 at 5:28 pm
Sweet script. Yours or one you found?
March 2, 2009 at 9:01 pm
ab5sr (3/2/2009)
Sweet script. Yours or one you found?
Thanks. Its mine
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
March 2, 2009 at 9:26 pm
Great job - dig it!
March 2, 2009 at 9:34 pm
There's nothing in the BOL index for dm_*. Any idea why not ? The MSDN website has info though:
http://msdn.microsoft.com/en-us/library/ms188754(SQL.90).aspx
March 2, 2009 at 10:34 pm
homebrew01 (3/2/2009)
There's nothing in the index for dm_*. Any idea why not ? The MSDN website has info though:http://msdn.microsoft.com/en-us/library/ms188754(SQL.90).aspx
I got it from querying the sysobjects table in master.
" SELECT * FROM master..sysobjects where type='IF' order by name "
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
March 3, 2009 at 12:44 am
Here is another useful script to monitor this. I can't claim credit, but it is incredibly similar to the other one posted here, formatted slightly different and with some additional info.
I found it here: http://www.wisesoft.co.uk/articles/tsql_backup_restore_progress.aspx
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')
September 25, 2009 at 11:59 am
so, how do we do the same thing in SQL server 2000?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply