January 30, 2015 at 2:57 am
At one of our client side a wired log shipping issues has come up.while monitoring those two server i noticed that although log-shipping report says both server are in sync, report displays information related to both backup and restore , it doesn't shows information related to copy that is when was last file copied and last file copy column is showed blank.same is when i execute proc "sp_help_log_shipping_monitor" . I get same result ...
When i expand copy job history to analyse its what i found is although job has executed successfully , but in depth reading each steps says that no .trn file was copied .
My copy directory is at secondary server itself where .trn files are placed.And from this location itself files are begin restored.
sql server and agent on both servers are run by same domain account
What could be issue . and what its solution
Regards
Anoop
January 30, 2015 at 5:05 am
Hello,
Just for a long shot:
Are you absolutely sure that log restores are happening on the secondary server?
Is your 'copy folder' is being populated with new files?
You can check the log restores by running this query on the secondary server if you have default trace enabled (which should be on by default)
SELECT StartTime,TextData,ObjectName,LoginName,ApplicationName FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1f.[value]
FROM sys.fn_trace_getinfo(1) f WHERE f.property = 2)), DEFAULT)
WHERE EventClass=115 and TextData LIKE 'RESTORE LOG%'
ORDER BY StartTime DESC;
Is there any chance that your primary server creates the log backups directly in the secondary server's copy folder?
This is the only way I could replicate the issue.
________________________________________________________________
"Accept conditions as they exist, or accept the responsibility for changing them."
February 3, 2015 at 10:36 pm
r.szasza (1/30/2015)
Hello,Just for a long shot:
Are you absolutely sure that log restores are happening on the secondary server?
Is your 'copy folder' is being populated with new files?
You can check the log restores by running this query on the secondary server if you have default trace enabled (which should be on by default)
SELECT StartTime,TextData,ObjectName,LoginName,ApplicationName FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1f.[value]FROM sys.fn_trace_getinfo(1) f WHERE f.property = 2)), DEFAULT)
WHERE EventClass=115 and TextData LIKE 'RESTORE LOG%'
ORDER BY StartTime DESC;
Is there any chance that your primary server creates the log backups directly in the secondary server's copy folder?
This is the only way I could replicate the issue.
hello r.szasza,
Yes log restore is happening .i have executed your code and it confirms that .
And yes my primary server creates the log backups directly in the secondary server's copy folder.
So is that issue
Regards
Anoop
February 3, 2015 at 11:52 pm
Does the user on the Primary Server through which SQL Services are executing have proper permissions on the SQL as well as the folder where the copied files resides on the Secondary server?
I faced the similar issue around 2 years back and the Root Cause was found to be the permission issue with the folder on the Secondary Server.
With Thanks,
Satnam
February 4, 2015 at 12:41 am
satnam.singh 44358 (2/3/2015)
Does the user on the Primary Server through which SQL Services are executing have proper permissions on the SQL as well as the folder where the copied files resides on the Secondary server?I faced the similar issue around 2 years back and the Root Cause was found to be the permission issue with the folder on the Secondary Server.
With Thanks,
Satnam
Hi Santam
Its not permission issue , I have double cross checked it
Regards
Anoop
February 4, 2015 at 1:01 am
anoop.mig29 (2/3/2015)
r.szasza (1/30/2015)
Hello,Just for a long shot:
Are you absolutely sure that log restores are happening on the secondary server?
Is your 'copy folder' is being populated with new files?
You can check the log restores by running this query on the secondary server if you have default trace enabled (which should be on by default)
SELECT StartTime,TextData,ObjectName,LoginName,ApplicationName FROM sys.fn_trace_gettable(CONVERT(VARCHAR(150), ( SELECT TOP 1f.[value]FROM sys.fn_trace_getinfo(1) f WHERE f.property = 2)), DEFAULT)
WHERE EventClass=115 and TextData LIKE 'RESTORE LOG%'
ORDER BY StartTime DESC;
Is there any chance that your primary server creates the log backups directly in the secondary server's copy folder?
This is the only way I could replicate the issue.
hello r.szasza,
Yes log restore is happening .i have executed your code and it confirms that .
And yes my primary server creates the log backups directly in the secondary server's copy folder.
So is that issue
Regards
Anoop
Hello Anoop,
Basically there is no copy occuring on the Secondary server, since the 'copy folder' always has the most recent log backups. This is not an issue, but there is a single point of failure if your Secondary server goes down, the Primary will not be able to create the log backups, and you lose the point in time restore possibility. Usually there is a network drive (or local drive on Primary) between the Primary and Secondary and that is why the copy job exists. You have to take care about the cleanup jobs' schedule though.
________________________________________________________________
"Accept conditions as they exist, or accept the responsibility for changing them."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply