May 10, 2011 at 1:25 am
I am running a backup and How much backup is completed and how much is rest. how to get this detail...
Thanks
May 10, 2011 at 1:41 am
If you use the WITH STATS option in your BACKUP DATABASE statement, you can monitor the progress of the operation.
John
May 10, 2011 at 1:48 am
Dear John Thanks,
I am getting the output in one stroke.Can I know it step by step...
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
51 percent processed.
60 percent processed.
70 percent processed.
81 percent processed.
90 percent processed.
Processed 9784 pages for database 'test2', file 'test2' on file 3.
100 percent processed.
Processed 11 pages for database 'test2', file 'test2_log' on file 3.
BACKUP DATABASE successfully processed 9795 pages in 3.239 seconds (23.625 MB/sec).
Thanks
May 10, 2011 at 1:52 am
I don't know what you mean. If your backup is only taking three seconds then why would you need to know what stage it's at?
John
May 10, 2011 at 1:56 am
For information...(I have run it on test DB,I have more than 500 GB databases.)
I am getting it in sqlcmd...
In wat type of commands with stats can be used?
Thanks
May 10, 2011 at 8:58 am
SELECT percent_complete, estimated_completion_time FROM sys.dm_exec_requests where session_id = 855
order by session_id
session_ID is the spid performing the process.
May 10, 2011 at 9:03 am
I forgot who is the owner of this code... not mine but works great :
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;
May 10, 2011 at 2:04 pm
This is the code I use to track backups and restores:
Select r.command
, s.text
, r.start_time
, r.percent_complete
, cast(((datediff(second, r.start_time, getdate())) / 3600) As varchar) + ' hour(s), '
+ cast((datediff(second, r.start_time, getdate()) % 3600) / 60 As varchar) + 'min, '
+ cast((datediff(second, r.start_time, getdate()) % 60) As varchar) + ' sec' As running_time
, cast((r.estimated_completion_time / 3600000) As varchar) + ' hour(s), '
+ cast((r.estimated_completion_time % 3600000) / 60000 As varchar) + 'min, '
+ cast((r.estimated_completion_time % 60000) / 1000 As varchar) + ' sec' As est_time_to_go
, dateadd(second, r.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');
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply