Log shipping not working

Have set up an incredibly simple log shipping scenrio before tackling the real thing.
Following this doc
http://technet.microsoft.com/en-us/library/ms190640(v=sql.105).aspx

I restored the (very small) DB to the secondary server.
Made a file share for the log backups on the primary.
No witness/monitor involved here.
Stepped through the LS wizard and set schedules.

I looked at the folder on the secondary server and the trn files are being copied across every 5 minute as should. Restore is not happening.

I checked the error log and found that the secondary DB was not in norecovery or stand by mode.
I restored the secondary DB again and left it in stand by mode.

Everything looks like it should be fine.
But...

None of the data changes I made in the primary show in the secondary.
When I check the sql error logs I see...

The log shipping secondary database xxx.xxx has restore threshold of 45 minutes and is out of sync. No restore was performed for 162 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information

I see no clues in the agent log on either server and there is no monitor.

Could this be a side effect of the secondary database being not norecover or stand by when I first setup LS?

Or is it because the secondary DB is currently showing standby/read only?
and if so why, the doco says it should be in standby mode
LVL 29
QPRAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

geek_vjCommented:
Have you checked the setting "Disconnect Users in the database while restoring backups" in the logshipping setup screen (this comes while configuring the standby server)

If this is not set/selected, then the log backups will pile up and wont be restored when there are connections to the standby database

Please check and revert so that this can be taken further accordingly
0
QPRAuthor Commented:
I'm fairly certain no1 will be connected to this DB - it didn't exist until I played with LS and there are no apps etc pointing to it
0
geek_vjCommented:
Pls check on the destination server to check for any connections on the destination database. Also check if atleast one log backup got restored onto the destination database after LS configuration.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Sagir87Commented:
Hi,

Please find the attachments it may be helpful.
All-Steps-in-Transaction-LS-with.docx
0
QPRAuthor Commented:
Looking at activity monitor - nobody is connected to the destination DB.
Zero transaction logs have been applied since the DB was created and LS configured.
The vaue I changed in the source DB does not show in the destination DB
0
QPRAuthor Commented:
I think I may have a clue, while re-looking at the config screens I saw a notice that it will not work if any other processes backup the transaction log for the primary DB.
I have a script that backs up the logs for all user DBs every hour. I hadn't thought of this.

I will exclude this DB from the backup script and restart the config. I will report back my findings
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
geek_vjCommented:
>>I think I may have a clue, while re-looking at the config screens I saw a notice that it will not work if any other processes backup the transaction log for the primary DB.
I have a script that backs up the logs for all user DBs every hour. I hadn't thought of this.

Excellent!! This is the perfect reason for LS not to work..Good that you found it out.
In future, please ensure that no other log backups are configured when LS is configured.
0
QPRAuthor Commented:
As I suspected I needed to disable other log backup options
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.