SQL 2005 Log Shipping Stops after a week

Hi,

Ive got two servers setup with a log shipping config for two tables which reside on the primiary database server. After a couple of days it stops syncing the bigger of the two databases. A quick reboot of the secondary server usually fixes this problem and the processing of the logs catches up.

Over the weekend, the server was rebooted to get the logs back up to date but for some reason, its not worked.

How can I resynv the process without removing the log shipping config and setting it back up?

Thanks
DW
daiwhyteAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Daniel_PLDB Expert/ArchitectCommented:
Is the second database used (one that logshipping stops), e.g. for reporting purposes?
0
daiwhyteAuthor Commented:
Yes, it always stops after a big Friday afternoon report - a quick reboot usually sorts it.
0
Daniel_PLDB Expert/ArchitectCommented:
This is because database which paticipates in log shipping cannot be used to restore transaction logs. You have the ability to drop all connections during restore (in log shipping settings) but it'll stop your reporting.
If reboot sorts your problem it means that database is able to restore all waiting tlog backups. I suggest you then after report is finished close all connections in that database.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Daniel_PLDB Expert/ArchitectCommented:
If you are sure when report finishes you can schedule restore job to don't run in the time when report is creating. You could also set restore options in log shipping to kill active connections to database while restoring tlog backups - warning: set restore to run after report is finished.
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
daiwhyteAuthor Commented:
Ok, here is the senario and maybe the solution.

My databases currently update every 15 minutes via log shipping. The Friday afternoon report starts at 1302 and finishes about 1325.

If I change my log transaction shipping to 30 minutes, this will give enough time for the report to run.

This still doesnt explain why the log shipping process doesn't restart after a reboot of the secondary sever.

0
TempDBACommented:
Is this anyhow related to log-backup size? what is the size on average and particularly on friday.
Is the recovery model set to full or bulk-logged?
0
Daniel_PLDB Expert/ArchitectCommented:
log shipping process doesn't restart after a reboot of the secondary sever.
You wrote that a quick reboot usually sorts it. If reboot of server resolve problem I think after report is finished there is still active connection to database preventing logs from restore.
0
daiwhyteAuthor Commented:
Ok, Ive set the restores to terminate any connections prior to updating the database and set the restore time frame outside of the report run time.

@TempDBA - the log backup size or small, no bigger the 48mb. The recover mode is set to Full.

0
daiwhyteAuthor Commented:
Thank you Daniel, using the opiton to close all active connections seems to have done the trick.
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 2005

From novice to tech pro — start learning today.