Viewing the remaining restore time for rebuilding log-shipping

I sometimes need to rebuild the log-shipping for a database and if it is a large database the windows dialogs only display the percentage during the backup phase of the rebuild. Once the process starts the restore of the database, the dialog just sits and spins until it is complete. Below is a query that will allow you to see the percentage of completion during the restore process and an estimated time in milliseconds (I will update the query to seconds in a future version)

First we need to find the SPID for the process that is performing the RESTORE:

-- Execute and find the SPID that is performing the RESTORE
EXEC sp_WhoIsActive;

1 2	-- Execute and find the SPID that is performing the RESTORE EXEC sp_WhoIsActive;

You will be able to identify this task by the login that the restore is running under. Typically this is an account name something like “log.shipper” or it could be a domain account set up specifically for this task.

Once we have identified the process and SPID it is running under, plug that number in the following query (replace the 90 number below with the actual SPID in your case).

NOTE: This query will work for ANY type of restore, whether rebuilding log-shipping or restoring a database (the latter will display the percentage complete in the dialog).

This SQL will allow you to just plug in the database name and it will find the SPID for you and return the results. It uses the famous Adam Mechanics sp_WhoIsActive. If you are not using this utility, you need to go here now Who is Active.

DECLARE @db_name AS VARCHAR(50);
DECLARE @searchString AS VARCHAR(100);

SET @db_name = 'MyDatabase';
SET @searchString = '%[' + @db_name + ']%';

CREATE TABLE #WhoIsActive
(
	spid smallint,
	sql_text XML
)

-- Insert the rows for active sessions
EXEC PLDBA.dbo.sp_WhoIsActive @output_column_list = '[session_id], [sql_text]', @DESTINATION_TABLE = #WhoIsActive;

-- Plug in the SPID from the previous WhoIsActive and run the query
SELECT	CONVERT(DECIMAL(4, 2), dmer.percent_complete) AS [% Complete],
				CONVERT(TIME, DATEADD(ms, dmer.estimated_completion_time, 0)) AS [Time Remaining]
	FROM sys.dm_exec_requests dmer
	INNER JOIN #WhoIsActive wia ON wia.spid = dmer.session_id
	WHERE CONVERT(VARCHAR(MAX), wia.sql_text) LIKE @searchString;

-- Cleanup
DROP TABLE #WhoIsActive

DECLARE @db_name AS VARCHAR(50);

DECLARE @searchString AS VARCHAR(100);

SET @db_name = 'MyDatabase';

SET @searchString = '%[' + @db_name + ']%';

CREATE TABLE #WhoIsActive

(

spid smallint,

sql_text XML

)

-- Insert the rows for active sessions

EXEC PLDBA.dbo.sp_WhoIsActive @output_column_list = '[session_id], [sql_text]', @DESTINATION_TABLE = #WhoIsActive;

-- Plug in the SPID from the previous WhoIsActive and run the query

SELECT CONVERT(DECIMAL(4, 2), dmer.percent_complete) AS [% Complete],

CONVERT(TIME, DATEADD(ms, dmer.estimated_completion_time, 0)) AS [Time Remaining]

FROM sys.dm_exec_requests dmer

INNER JOIN #WhoIsActive wia ON wia.spid = dmer.session_id

WHERE CONVERT(VARCHAR(MAX), wia.sql_text) LIKE @searchString;

-- Cleanup

DROP TABLE #WhoIsActive

otherwise you can run it manually as displayed below:

-- Plug in the SPID from the previous WhoIsActive and run the query
SELECT	CONVERT(DECIMAL(4,2), dmer.percent_complete) AS [% Complete],
				CONVERT(TIME, DATEADD(ms, dmer.estimated_completion_time, 0)) AS [Time Remaining]
	FROM sys.dm_exec_requests dmer
	WHERE dmer.session_id = 90;

-- Plug in the SPID from the previous WhoIsActive and run the query

SELECT CONVERT(DECIMAL(4,2), dmer.percent_complete) AS [% Complete],

CONVERT(TIME, DATEADD(ms, dmer.estimated_completion_time, 0)) AS [Time Remaining]

FROM sys.dm_exec_requests dmer

WHERE dmer.session_id = 90;

Views – 2154

LincolnBlog.net

…my observations in the world of SQL Server, PowerShell, .Net etc…

Viewing the remaining restore time for rebuilding log-shipping