To check the status of the Database while restoring

if OBJECT_ID('tempdb..#tmp_Restores') is null
begin
SELECT  @@servername as ServerName
 , r.command as [CMD_Type]
 , r.start_time
 , r.percent_complete
 , dateadd(second, r.estimated_completion_time / 1000, getdate()) as est_completion_time
 /* Remark: 1900-01-01 00:00:00.000 offset !!! */
 , dateadd(ss, DATEDIFF(SS, r.start_time, getdate()), 0) as running_time_1900_01_01
 , dateadd(ss, r.estimated_completion_time / 1000, 0) as est_time_to_go_1900_01_01
 , dateadd(ss, DATEDIFF(SS, r.start_time, dateadd(second, r.estimated_completion_time / 1000, getdate())), 0) as est_elaps_time_1900_01_01
 , es.login_name
 , es.host_name
 , r.wait_type
 , r.wait_time
 , r.wait_resource
 , r.cpu_time
 , r.total_elapsed_time
 , r.reads
 , r.writes
 , r.logical_reads
 , s.text as SQLStmt
into #tmp_Restores
FROM    sys.dm_exec_requests r
inner join sys.dm_exec_sessions es
on es.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE   r.command in ( 'RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG' )

end

Insert into #tmp_Restores
SELECT  @@servername as ServerName
      , r.command as [CMD_Type]
      , r.start_time
      , r.percent_complete
      , dateadd(second, r.estimated_completion_time / 1000, getdate()) as est_completion_time
      /* Remark: 1900-01-01 00:00:00.000 offset !!! */
      , dateadd(ss, DATEDIFF(SS, r.start_time, getdate()), 0) as running_time_1900_01_01
      , dateadd(ss, r.estimated_completion_time / 1000, 0) as est_time_to_go_1900_01_01
      , dateadd(ss, DATEDIFF(SS, r.start_time, dateadd(second, r.estimated_completion_time / 1000, getdate())), 0) as est_elaps_time_1900_01_01
      , es.login_name
      , es.host_name
      , r.wait_type
 , r.wait_time
 , r.wait_resource
 , r.cpu_time
 , r.total_elapsed_time
 , r.reads
 , r.writes
 , r.logical_reads

      , s.text as SQLStmt
-- into #tmp_Restores
FROM    sys.dm_exec_requests r
inner join sys.dm_exec_sessions es
        on es.session_id = r.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE   r.command in ( 'RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG' )
-- order by percent_complete desc


Select *
from #tmp_Restores
order by percent_complete desc

Comments

Post a Comment

Popular posts from this blog

TSQL To Get All the Names of tables and Databases whose data have changed in last 24 Hours

-- ============================================= -- Author: Muhammad Jawad -- Create date: 2/2/2017 -- Description: To Get All the Names of tables and Databases whose data have changed in last 24 Hours -- ============================================= ALTER PROC UP_FindAllDatabaseHaveChanges AS     BEGIN         DECLARE @loopStart INT= 1;         DECLARE @looend INT;         IF OBJECT_ID('tempdb..#AllDatabaseTables') IS NOT NULL             BEGIN                 DROP TABLE #AllDatabaseTables;             END;         CREATE TABLE #AllDatabaseTables             (               ID INT IDENTITY(1, 1)                      PRIMARY KEY ,       ...
Read more

To Get All the Names of tables and Databases their data have changed in last 24 Hours And Backup those databases

-- ============================================= -- Author: Muhammad Jawad -- Create date: 2/3/2017 -- Description: To Get All the Names of tables and Databases their data have changed in last 24 Hours And Backup those databases -- ============================================= Alter PROC UP_FindAndBackupAllDatabaseHaveChanges AS     BEGIN ----**************************START: To get all the tables and database names whose data have changed in last 24 hours **************************----         DECLARE @loopStart INT= 1;         DECLARE @looend INT;         IF OBJECT_ID('tempdb..#AllDatabaseTables') IS NOT NULL             BEGIN                 DROP TABLE #AllDatabaseTables;             END;         CREATE TABLE #AllDatabaseTables             ( ...
Read more

Apply paging in procedure

@intPageId int, @intPageSize int select * from ( Select     row_number() over (order by  TableA.intID asc ) r ,     TableA.intID,tableA.hName,TableB.Address  from TableA   Join TableB on TableA.intid = TableB.intId   ) A   where A.r  between ( (@intpageid-1)*@intpagesize)+1 and ((@intpageid-1)*@intpagesize)+@intpagesize The query in bold text is for paging, and the normal text query is that which returns your records. OR SELECT * from PressRelease  order by CreatedDate desc OFFSET (@PageID-1)*@PageSize ROWS FETCH NEXT @PageSize ROWS ONLY
Read more