Backup or Restore progess

,

This procedure provides the information like when the backup/Restore for a particular database is started and what is the average time it will take to complete and from how long it is running and by when it will be completed. With this information we can wait for the average shown time for the completion of the task and we can take up other challenges instead of waiting on this for long time.

USE [master]

GO

/****** Object: StoredProcedure [dbo].[prDBA_Check_BackuporRestoreProgress] Script Date: 02/19/2010 05:04:17 ******/

IF EXISTS (SELECT * FROM sys.objects 

WHERE object_id = OBJECT_ID(N'[dbo].[prDBA_Check_BackuporRestoreProgress]') 

AND type in (N'P', N'PC'))

DROP PROCEDURE [dbo].[prDBA_Check_BackuporRestoreProgress]

GO

/****** Object: StoredProcedure [dbo].[prDBA_Check_BackuporRestoreProgress] Script Date: 02/19/2010 05:03:31 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE proc [dbo].[prDBA_Check_BackuporRestoreProgress]

as 

BEGIN



SELECT @@servername, 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')

END

Rate

3.63 (8)

Share

Share

Rate

3.63 (8)