[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


SQL Log Shipping Question

Posted on 2015-02-23
Medium Priority
Last Modified: 2015-03-02
Hi Experts,
I have a question about SQL Server Log Shipping:
I have set up a log shipping between two databases on two SQL Server instances (SQL 2008 R2).
It has been working with no issues  - every transaction is being replicated from the primary to the secondary instance in terms of minutes - all log shipping jobs are running successfully and on time.

Then I decided to test this with DDL, namely creating a new table in the primary instance.

I was surprised to see that 3 days later the table is still not showing up in the secondary database!
Log shipping is running and jobs are running as scheduled with no issues.

I was under the impression that log shipping creates an exact copy of the primary on the secondary and every transaction, DML OR DDL (created objects) will also be replicated.

Why then I do not see the newly created table from the primary to be replicated to the secondary?

What am I missing? Is there an option in LS to tell the process to also replicate objects, schema changes etc?

Please help with information or specific sources of information on this mystery :-)


P.S. Please refrain from questions like "Why are you using Log Shipping when...." and "Why don't you use this and that instead...". I am aware of the alternatives of database replication, in fact I have tested at least 9 other alternatives (SQL Server Replication, Backup/ Restore, CDC, Database Mirroring, Custom ETL etc). I am using Log Shipping because I have a reason to use it and cannot use any other method in this specific case>
Question by:RICuser
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
LVL 52

Expert Comment

by:Vitor Montalvão
ID: 40627920
I was surprised to see that 3 days later the table is still not showing up in the secondary database!
That's weird. The table still exists in the primary database?
Also, others data modifications has been applied to the secondary database?
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 40628188
I am guessing that you are using ReadOnly/Standby mode if you are looking at the log shipped data.

There is really no magic in Log Shipping because it is the same as backup / restore so there is no possibility that there are transactions that did not make it unless the transaction was rolled back.  If it is in the primary, then it was committed and should show up in the secondary.

If you can confirm that you are using Standby mode then you could right click on the database in Management Studio and choose Properties and look at the last Log Backup date.  You should see a recent date or in this case, a 3 day ago date if the data is not there.

Let me know, I am veteran with Log Shipping and I am happy to take you through all this to see what could be wrong.

Author Comment

ID: 40628968
Thank you Ben, yes the secondary is in stand by\read only mode.
I have attached some screenshots of the properties you've asked for.
Also, here are the versions of the two instances (not sure whether this makes any difference):

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)   Jun 17 2011 00:54:03

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1734.0 (X64)   Aug 11 2010 12:51:02

Thank you!
RIC User
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 40628986
Yes, Vitor The table is still in the primary but not in the secondary database.
I wonder what will happen if I run some UPDATEs on that table in teh primary (since all other transactions are being replicated...)
I am going to try this now and will post  results.

LVL 25

Accepted Solution

DBAduck - Ben Miller earned 2000 total points
ID: 40629042
OK, it looks like you either ran the restore on the secondary with a different name or there have been no restores on the other side.

Try this query on the secondary:
select top 100 *
from msdb.dbo.restorehistory
where restore_type = 'L' and destination_database_name = 'YOUR DATABASE NAME HERE'
order by restore_date desc

Open in new window

See if there are any results.  Also you should not have version mismatches especially on the secondary side.  You have a lower version of SQL (service pack RTM) than the primary (SP1) and that is not a favorable situation in the restore process.

Let me know what you find.  It appears to me from what you have posted that there have been no log restores.

Author Comment

ID: 40629336
Ben, the query did not return any results. That tells me that there were no successful restores for some time.
I checked the LS Monitor job and the error confirmed that - see attached.
Where can I find more information as to why the restores are failing and are there any tools (like say, Replication Monitor for SQL replication) that would help monitor the Log Shipping process?

LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 40629356
The restore information will be in the Event Viewer on the server and should be in the ERROR log for SQL Server.

Let me know.

Author Comment

ID: 40629419
...cont from previous comment...
And still the restore job on the secondary  shows a successful restore.
Isn't that misleading? (see attached)

Author Comment

ID: 40629436
Tge SQL Server log is flooded with these messages starting from th 17th and still going on:

Date            2/17/2015 1:34:00 PM
Log            SQL Server (Current - 2/22/2015 3:14:00 AM)

Source            spid295

The log shipping secondary database <database_name> has restore threshold of 45 minutes and is out of sync. No restore was performed for 47 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information.
LVL 25

Expert Comment

by:DBAduck - Ben Miller
ID: 40629466
Yes, it is misleading as it says that 0 files were restored.

This appears that Log Shipping is not set up correct. What does the dialog look like in the primary database Transaction Log Shipping tab in the Properties?

Author Closing Comment

ID: 40640064
The query provided by Ben Miller pointed out an issue with the restores - I found no successful restores to the secondary database.
I resolved this by reconfiguring Log Shipping from scratch and now all works as intnded.
The only difference from my first time setup was that then I used an already generated backup for the secondary initialization.
This time around I let SQL Server to create its first backup  - an option as part of the Log Shipping setup.
With its own initial backup\restore now the secondary is being updated with new transactions and also with DDL as well.
Thanks Ben!


Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question