• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 277
  • Last Modified:

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
0
daiwhyte
Asked:
daiwhyte
  • 4
  • 4
1 Solution
 
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now