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
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
Daniel_PL
Is the second database used (one that logshipping stops), e.g. for reporting purposes?
ASKER
Yes, it always stops after a big Friday afternoon report - a quick reboot usually sorts it.
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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?
Is the recovery model set to full or bulk-logged?
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.
ASKER
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.
@TempDBA - the log backup size or small, no bigger the 48mb. The recover mode is set to Full.
ASKER
Thank you Daniel, using the opiton to close all active connections seems to have done the trick.