Faster Log Shipping Restores

  • Comments posted to this topic are about the item Faster Log Shipping Restores

  • Kind of interesting. Thanks.

  • This is working for me fine on one Secondary but on another I get the error below.

    Error converting data type nvarchar to bigint. [SQLSTATE 42000] (Error 8114). The step failed.

  • I'm getting this when I execute the proc from SSMS.

    Msg 512, Level 16, State 1, Procedure CustomLogShippingRestore, Line 166

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    Msg 512, Level 16, State 1, Procedure CustomLogShippingRestore, Line 147

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    Msg 22022, Level 16, State 1, Line 22

  • Hello!

    I'm going to take a look at these issues shortly and will get back to you.

  • With regards to the error "Error converting data type nvarchar to bigint. [SQLSTATE 42000] (Error 8114). The step failed." I cannot really help without more info, but I think I can help with your second post.

    I believe the issue here is the part below (it's repeated several times through the script)

    WHILE (

    SELECT 1

    FROM msdb.dbo.sysjobs_view job

    INNER JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id

    WHERE activity.run_Requested_date IS NOT NULL

    AND activity.stop_execution_date IS NULL

    AND job.NAME = @RestoreJobName

    ) = 1

    BEGIN

    WAITFOR DELAY '00:00:00:100';

    END;

    EXECUTE msdb.dbo.sp_start_job @RestoreJobName;

    You most likely have orphaned entries in the sysjobactivity table for your restore job. I don't have such an issue myself as my log shipping setup is fairly new.

    As I don't have any orphaned entries I cannot properly test this, but I am adding this to the script I run in case it ever does

    Get the session id of the current SQL Agent session (I added this near the top of the sproc.)

    DECLARE @maxSessionId INT

    SELECT @maxSessionId = MAX(session_id)

    FROM msdb.dbo.syssessions

    Then replace the code at the top of this entry with below:

    WHILE (

    SELECT 1

    FROM msdb.dbo.sysjobs_view job

    INNER JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id

    WHERE activity.run_Requested_date IS NOT NULL

    AND activity.stop_execution_date IS NULL

    AND job.NAME = @RestoreJobName

    AND activity.session_id = @maxSessionId

    ) = 1

    BEGIN

    WAITFOR DELAY '00:00:00:100';

    END;

    EXECUTE msdb.dbo.sp_start_job @RestoreJobName;

    This will only return the rows for the currently running session, which is the only one we care about.

    Let me know if this resolves the issue.

  • Thanks for the followup and extra help.

  • Two years on, but I'm implementing this today 🙂
    The error (Error converting data type nvarchar to bigint) occurs when the secondary database name is different from primary.
    I fixed this by adding an additional parameter.

    ALTER PROCEDURE [dbo].[CustomLogShippingRestore]
         @RestoreJobName SYSNAME -- we kick off the restore job manually for each backup file to be restored
        ,@OrigDatabase SYSNAME -- original name of database on Primary server
        ,@Database SYSNAME -- name of db that is to be restored on secondary server
    AS

    Change the following two lines in the stored procedure (replace @Database with @OrigDatabase)

    SET @FolderDbNameLength = @FolderDbNameLength + LEN(@OrigDatabase) + 1;

    SET @RightTrimForBackupInt = LEN(@OrigDatabase) + @FileTypeLength + 1;--.trn and trailing undercarriage

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply