Not Updating log_shipping_databases

Question

Not Updating log_shipping_databases

Jayakumar Krishnan

SSCarpal Tunnel

Points: 4710
More actions
June 4, 2006 at 7:26 pm

#60958

Hi all,
There is a logshipping setup, in the secondary server in the log_shipping_databases table the columns Date_Last_Copied and Date_Last_Loaded are not updating properly. In the Monitoring server Date_Last_Copied, Date_Last_Loaded,Last_File_Copied and Last_File_Loaded are not updating.
I'm accessing a share drive from the primary server. The LS is in Sync and Last_File_Copied and Last_File_Loaded columns are updating properly.
I've verified the service account for the servers, it is holding sa permission.
What could be the reasons for the above issue?
Pls help me.
warmest
Jayakumar K.
Thanks
Jay
http://www.sqldbops.com

Viewing 5 posts - 1 through 4 (of 4 total)

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

sivaprasad SSCertifiable Points: 6183 More actions · Answer 1

Jayakumar,

It could be an indication that the primary_server_name column of log_shipping_primaries or the secondary_server_name column of log_shipping_secondaries does not reflect the proper names.

Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

Jayakumar Krishnan SSCarpal Tunnel Points: 4710 More actions · Answer 2

Extremely sorry to all, Thanks Siva for your correction.

In Sec. Server : Lg_shipping_Plan_databases

Columns: Date_last_Copied,Date_Last_Loaded

In Monitoring Server : Log_shipping_Secondaries

Columns Last_copied_filename,Last_loaded_Filename

Last_Copied_Last_Updated

Last_Loaded_Last_Updated

The above columns are not updating, but the logshipping is in Sync.

What could be the reasons. Please correct me whare i did mistake?

I'm using shared path in the Priamry Server. I don't think that could be the reason.

warmest

Jayakumar K.

Thanks
Jay
http://www.sqldbops.com

Robert Hill-311095 Old Hand Points: 339 More actions · Answer 3

I had a similar problem when setting up log shipping. The root cause was Enterprise manager had my primary server registered as (Local) instead of the actual server name. Then the log shipping stored procedure would use the actual server name and would not find a match when trying to update the table.

My solution was to ensure all servers envolved in the log shipping have their actual server names used when registered in EM.

Then setup log shipping from scratch.

You could modify the table directly to correct the issue also.

Clean Up Notes: (to completely remove log shipping)

If you remove log shipping using the option on the maint package, it sometimes leaves some rows.

These simple stmts will clean up all log shipping rows.

Also ensure all jobs have been deleted.

Use msdb

Delete FROM [msdb].[dbo].[log_shipping_secondaries]

Delete FROM [msdb].[dbo].[log_shipping_primaries]

Delete FROM [msdb].[dbo].[log_shipping_plans]

Delete FROM [msdb].[dbo].[log_shipping_plan_history]

Delete FROM [msdb].[dbo].[log_shipping_plan_databases]

Delete FROM [msdb].[dbo].[log_shipping_monitor]

Delete FROM [msdb].[dbo].[log_shipping_databases]

Go

Use msdb

SELECT * FROM [msdb].[dbo].[log_shipping_secondaries]

SELECT * FROM [msdb].[dbo].[log_shipping_primaries]

SELECT * FROM [msdb].[dbo].[log_shipping_plans]

SELECT * FROM [msdb].[dbo].[log_shipping_plan_history]

SELECT * FROM [msdb].[dbo].[log_shipping_plan_databases]

SELECT * FROM [msdb].[dbo].[log_shipping_monitor]

Go

Jayakumar Krishnan SSCarpal Tunnel Points: 4710 More actions · Answer 4

Robert you're correct, in this case we've to careful while deleting the same in Secondary and Monitoring servers. Bcaz those servers could be configured with some other servers. In Secondary Log_shipping_Plans table Primary_server_name column will have (Local) instead of server name and in Monitoring server Log_shipping_Primaries table Primary_server column will have (local) instead of server name. So we should be very keen before execute the DELETE, shoud be with WHERE clause.

In my case I've registered the server with proper name, one more thing I've to mention here, that is the SQL service is running under LOCAL account and SQL Agent is running under Domain account. As per my concern this will not be a problem bcaz all jobs are taking care by Agent, agent account only connecting to the secondary server and update it. It is updating Last file copied and Loaded but it is not updting times also in monitoring server.

I don't know where I did mistake

Jayakumar K

Thanks
Jay
http://www.sqldbops.com