November 30, 2015 at 12:37 pm
Comments posted to this topic are about the item Faster Log Shipping Restores
December 14, 2015 at 6:31 am
Kind of interesting. Thanks.
December 14, 2015 at 6:21 pm
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.
December 14, 2015 at 6:37 pm
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
December 15, 2015 at 2:36 am
Hello!
I'm going to take a look at these issues shortly and will get back to you.
December 15, 2015 at 5:52 am
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.
April 25, 2016 at 4:52 pm
Thanks for the followup and extra help.
July 25, 2018 at 9:58 pm
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply