SQL Server Performance Forum – Threads Archive

Out of Sync again!!

Hi all,
I am an Oracle dba but I have had SQL thrust upon me (kicking & screaming, NOT REALLY!!) & I am having a few log shipping problems. Here is what#%92s happening: Both SQL 2000 SP3 servers registered on each other.
All SQL Services configured to use the same domain account.
Both clocks are in sync.
Transaction Log backup from source server scheduled every 10 minutes.
Copy frequency every 15 minutes.
Load frequency 20 minutes.
Out of sync threshold 45 minutes.
Load time delay 0 minutes.
File retention 2 hours.
History retention 2 days.
All jobs run successfully but still getting ‘out of sync error#%92 even though I know they#%92re not. Every transaction log has been copied and applied (secondary server log shipping history confirms this).
Secondary SQL Server logs have error: “The log shipping source (local).<dbname> has not been backed up for xxx minutes.” Any help would be much appreciated as I am really struggling.
Vic

---

Check: http://support.microsoft.com/default.aspx?scid=kb;en-us;329133 HTH Luis Martin
Moderator
SQL-Server-Performance.com

---

Valid article by Luis and ensure between primary and secondar server there are no network issues which might cause these inconsistency of applying logs on secondary server. Also on the part of service pack and MDAC versions, make sure no issues and similar versions should be levelled. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Many thanks guys, I had a look at the Microsoft website before submitting my question with the forum. I can say for sure the patch levels are the same & there are no network isues. Do my timings look ok? Once again thanks
Vic

---

Fine with the settings, and BTW how big the transaction log backup file in terms of peak level and off-peak level in a day. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

The transaction logs vary from about 300k – 1meg at the biggest. Vic

---

All I have discovered that the destination database is in Read-only mode which makes sense. The table log_shipping_primaries in the msdb database only has one record. Does this mean the tables are not being updated? The only entry in the log_shipping_primaries table is "first_file_000000000000.trn" Should the database access be "restrict access" Many thanks
Vic

---

There will be only one primary server details as per the log shipping architecture. Run sp_get_log_shipping_monitor_info to get more information on the pair. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Hi there, I have run sp_get_log_shipping_monitor_info and saved it as a file, would anybody be interested in having a look at it & letting me know if I have really done something stupid. Thanks Vic

---

If the text is ain’t big enough then post here (say not more than 100 lines), otherwise email me to [email protected] for assessment. BTW, did you see any errors or information pertaining to this failure?
Do you get any network issues between primary and standby servers during the log shipping process? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Satya- I’m having the same problems described in this thread, and I have seen you recommend running "sp_get_log_shipping_monitor_info " for more info on thsi and similar threads. Can you explain how to do this step by step? I only know Oracle, and it’s been 5+ years since I’ve used it. Where can I get a SQL prompt on a windows 2003 machine running SQL Server 2000? I’m assuming I need to execute this from a SQL prompt. Thanks, -P

---

Well, in that case Books online will be the best resource to refer the syntax, command line operations for SP_GET_LOG_SHIPPING_MONITOR_INFO.
You can run this statement in query analyzer by supplying the database, which was participated in log shipping pair. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Thanks for your help. I tried to run ‘SP_GET_LOG_SHIPPING_MONITOR_INFO’ and the Stored Procedure was not found, then had someone look into this who knows more about SQL than I do and they said there is no Stored Procedure by this name in the database(s) we use for log shipping. What am I misssing? -P

---

What version of SQL and edition is used?
And its service pack?
It resides in MSDB database. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

We are running SQL Server 2000 (8.00.760) Enterprise Edition (SP3) on a Windows 2003 Server (Enterprise). I was able to run that query on the msdb database as suggested. Can someone take a look at the data? Let me know if I should post it or who I can email it to. Thanks! -P

---

I think you must have configured log shipping by registering servers by I/P address and not by name. Because of that the entries in the log shipping data dictionary tables (log_shipping_secondaries and log_shipping_primaries) under the column secondary_server_name and primary_server_name the names must be I/P addresses. It must be server name. Because while updating these tables with latest generated / restored file names ,sql servers first select the record from this tables by using @@servername and the query returns 0 records. And it does not update the record with latest file name.
The solution is just change the entries with server name.
Hope this solves your problem.
-Anup

---

Post here and see any information that helps to assess. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

This might help, I seem to be backing up and copying the datatbase OK at this point (mostly, they get out of synch during the business day, but always catch up after hours), but this error occurs in the logs for every restore attempt. I did copy over a fresh backup copy of the database to the secondary server right before I turned on log shipping. It took a few hours to copy the database over to the secondary server, could this be the problem? [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set begins at LSN 6513000000261000001, which is too late to apply to the database. An earlier log backup that includes LSN 6510000000031400001 can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally. And here’s the results of running ‘SP_GET_LOG_SHIPPING_MONITOR_INFO’: primary_server_name: SQLREMIDIS
primary_database_name: DIGBLDG
secondary_server_name: SQLPROD3
secondary_database_name: DIGBLDG
backup_threshold: 45
backup_threshold_alert: 14420
backup_threshold_alert_enabled: 1
last_backup_filename: DIGBLDG_tlog_200406071815.TRN
last_backup_last_updated: 15:17.7
backup_outage_start_time: 0
backup_outage_end_time: 0
backup_outage_weekday_mask: 0
backup_in_sync: -1
backup_delta: 1523
last_copied_filename: DIGBLDG_tlog_200406071815.TRN
last_copied_last_updated: 30:18.7
last_loaded_filename: first_file_000000000000.trn
last_loaded_last_updated: 50:15.7
copy_delta: 0
copy_enabled: 1
load_enabled: 1
out_of_sync_threshold: 45
load_threshold_alert: 14421
load_threshold_alert_enabled: 1
load_outage_start_time: 0
load_outage_end_time: 0
load_outage_weekday_mask: 0
load_in_sync: -1
load_delta: 4045
maintenance_plan_id: 387B1882-7E57-469A-AFE0-D8EBD68619E1
secondary_plan_id: 674657B4-08DB-48C8-A87E-8D95F0E04C19 Thanks.

---

When configuring log shipping did you under "Initialize the Destination Databases" specify the option "Take full database backup now"? Try that so that you are sure that the databases are in sync. When it comes to alerts triggering even when it is working this is from a Microsoft KB:
———
Q1: Log Shipping Backup and Out of Sync alerts are firing, even when the secondary server is updated with the transaction log backups. Is this possible? A1: Yes. It is possible that the alerts might fire even when the secondary database is being updated. If the alert threshold is set to a value less than double the time between back up and copy or restore jobs, the alerts might be raised. If the alerts are being raised and the threshold is close to or less than two times the time between subsequent backup and copy or restore jobs, go ahead and increase the threshold.
——— You say the database takes hours to copy, how big is the backup file?

---

OK, I will break and reconfigure log shipping once more to try the ‘Take full database backup now’ option. I don’t remember that option, but I can double check. Should I do this with or without manually backing upcopying
estoring the database beforehand? The ‘alerts triggering’ section you recommended does not apply to us as the secondary server is definitely not updating. The backup file is 700 MB or so. The actualy database is bigger once restored/running, over a Gig. Thanks!

---

Ensure you don’t have network issues better primary and secondary server, it seems the main problem starts here where the log shipping process is out of sync. due to file copy process. What is the interval of log shipping restore?
As a fresh start can you re-define log shipping process from scratch for this database? Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Argyle: I tried re-configuring log shipping and did not see an option to "Take full database backup now" under "Initialize the Destination Databases". All I saw was the option to resotre to an existing database or create a new one. I restored to the existing, which I copied from the source server and recreated myself a few minutes before turning on log shipping. Are we on the same version of the Maintenance Plan wizard? I don’t appear to have network issues between the servers. I have checked the ‘User Rights Assignment’ in the Local Security Policy for the account that is used for the Log shipping and it has all the proper rights. The log files are being copied just fine. If you can give me a detailed and specific list of things to check I would love to receive it and check. The two area where I do seem to have trackable problems are: 1. Backup. I ask the system to do a backup of the Trans logs every 15 minutes, yet I get the details listed at the end of this posting. 2. Restore. Does not seem to be working due to this error (reported before): Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set begins at LSN 6513000000261000001, which is too late to apply to the database. An earlier log backup that includes LSN 6510000000031400001 can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally. The restore is set to fire every 15 minutes. I have removed and recreated the Database Maintenance plan numerous times, completely redefining log shipping each time. I still have problems with the import, and occasionally, the backup. Look at this example from the directory where the backup files are created. Why the big gaps when I ask the server to backup the logs every 15 minutes? Volume in drive S is SQL
Volume Serial Number is B032-69EA Directory of S:LogFiles 06/11/2004 06:25 PM <DIR> .
06/11/2004 06:25 PM <DIR> ..
06/04/2004 11:00 PM 260,608 DIGBLDG_tlog_200406042300.TRN
06/05/2004 02:30 AM 5,247,488 DIGBLDG_tlog_200406050230.TRN
06/05/2004 02:45 AM 260,608 DIGBLDG_tlog_200406050245.TRN
06/05/2004 03:00 AM 260,608 DIGBLDG_tlog_200406050300.TRN
06/05/2004 03:15 AM 260,608 DIGBLDG_tlog_200406050315.TRN
06/05/2004 04:00 AM 2,161,152 DIGBLDG_tlog_200406050400.TRN
06/05/2004 04:15 AM 260,608 DIGBLDG_tlog_200406050415.TRN
06/05/2004 04:30 AM 260,608 DIGBLDG_tlog_200406050430.TRN
06/05/2004 06:00 AM 260,608 DIGBLDG_tlog_200406050600.TRN
06/05/2004 06:15 AM 260,608 DIGBLDG_tlog_200406050615.TRN
06/05/2004 10:00 AM 260,608 DIGBLDG_tlog_200406051000.TRN
06/05/2004 11:30 AM 260,608 DIGBLDG_tlog_200406051130.TRN
06/05/2004 11:45 AM 260,608 DIGBLDG_tlog_200406051145.TRN
06/05/2004 12:00 PM 260,608 DIGBLDG_tlog_200406051200.TRN
06/05/2004 12:15 PM 260,608 DIGBLDG_tlog_200406051215.TRN
06/05/2004 12:45 PM 260,608 DIGBLDG_tlog_200406051245.TRN
06/05/2004 02:15 PM 260,608 DIGBLDG_tlog_200406051415.TRN
06/05/2004 02:30 PM 260,608 DIGBLDG_tlog_200406051430.TRN
06/05/2004 02:45 PM 260,608 DIGBLDG_tlog_200406051445.TRN
06/05/2004 03:00 PM 260,608 DIGBLDG_tlog_200406051500.TRN
06/05/2004 03:15 PM 260,608 DIGBLDG_tlog_200406051515.TRN
06/05/2004 03:45 PM 260,608 DIGBLDG_tlog_200406051545.TRN
06/05/2004 04:00 PM 260,608 DIGBLDG_tlog_200406051600.TRN
06/05/2004 08:30 PM 260,608 DIGBLDG_tlog_200406052030.TRN
06/05/2004 08:45 PM 260,608 DIGBLDG_tlog_200406052045.TRN
06/05/2004 09:00 PM 260,608 DIGBLDG_tlog_200406052100.TRN
06/05/2004 09:30 PM 260,608 DIGBLDG_tlog_200406052130.TRN
06/05/2004 09:45 PM 260,608 DIGBLDG_tlog_200406052145.TRN
06/05/2004 10:00 PM 260,608 DIGBLDG_tlog_200406052200.TRN
06/05/2004 10:15 PM 260,608 DIGBLDG_tlog_200406052215.TRN
06/05/2004 10:30 PM 260,608 DIGBLDG_tlog_200406052230.TRN
06/06/2004 12:30 AM 260,608 DIGBLDG_tlog_200406060030.TRN
06/06/2004 12:45 AM 260,608 DIGBLDG_tlog_200406060045.TRN
06/06/2004 01:00 AM 19,879,424 DIGBLDG_tlog_200406060100.TRN
06/06/2004 01:15 AM 231,936 DIGBLDG_tlog_200406060115.TRN
06/06/2004 01:30 AM 231,936 DIGBLDG_tlog_200406060130.TRN
06/06/2004 04:30 AM 231,936 DIGBLDG_tlog_200406060430.TRN
06/06/2004 06:30 AM 231,936 DIGBLDG_tlog_200406060630.TRN
06/06/2004 06:45 AM 231,936 DIGBLDG_tlog_200406060645.TRN
06/06/2004 07:00 AM 231,936 DIGBLDG_tlog_200406060700.TRN
06/06/2004 07:15 AM 231,936 DIGBLDG_tlog_200406060715.TRN
06/06/2004 09:30 AM 231,936 DIGBLDG_tlog_200406060930.TRN
06/06/2004 09:45 AM 231,936 DIGBLDG_tlog_200406060945.TRN
06/06/2004 10:00 AM 231,936 DIGBLDG_tlog_200406061000.TRN
06/06/2004 11:30 AM 231,936 DIGBLDG_tlog_200406061130.TRN
06/06/2004 01:45 PM 231,936 DIGBLDG_tlog_200406061345.TRN
06/06/2004 02:00 PM 231,936 DIGBLDG_tlog_200406061400.TRN
06/06/2004 02:15 PM 231,936 DIGBLDG_tlog_200406061415.TRN
06/06/2004 03:45 PM 231,936 DIGBLDG_tlog_200406061545.TRN
06/06/2004 04:00 PM 231,936 DIGBLDG_tlog_200406061600.TRN
06/06/2004 05:30 PM 231,936 DIGBLDG_tlog_200406061730.TRN
06/06/2004 05:45 PM 231,936 DIGBLDG_tlog_200406061745.TRN
06/06/2004 06:15 PM 231,936 DIGBLDG_tlog_200406061815.TRN
06/06/2004 06:30 PM 231,936 DIGBLDG_tlog_200406061830.TRN
06/06/2004 06:45 PM 231,936 DIGBLDG_tlog_200406061845.TRN
06/06/2004 07:00 PM 231,936 DIGBLDG_tlog_200406061900.TRN
06/06/2004 07:45 PM 231,936 DIGBLDG_tlog_200406061945.TRN
06/06/2004 08:00 PM 231,936 DIGBLDG_tlog_200406062000.TRN
06/06/2004 08:15 PM 231,936 DIGBLDG_tlog_200406062015.TRN
06/06/2004 08:30 PM 231,936 DIGBLDG_tlog_200406062030.TRN
06/06/2004 11:30 PM 231,936 DIGBLDG_tlog_200406062330.TRN
06/07/2004 12:00 AM 231,936 DIGBLDG_tlog_200406070000.TRN
06/07/2004 12:15 AM 231,936 DIGBLDG_tlog_200406070015.TRN
06/07/2004 12:30 AM 231,936 DIGBLDG_tlog_200406070030.TRN
06/07/2004 12:45 AM 231,936 DIGBLDG_tlog_200406070045.TRN
06/07/2004 01:00 AM 231,936 DIGBLDG_tlog_200406070100.TRN
06/07/2004 01:45 AM 231,936 DIGBLDG_tlog_200406070145.TRN
06/07/2004 02:00 AM 231,936 DIGBLDG_tlog_200406070200.TRN
06/07/2004 02:15 AM 231,936 DIGBLDG_tlog_200406070215.TRN
06/07/2004 04:30 AM 231,936 DIGBLDG_tlog_200406070430.TRN
06/07/2004 04:45 AM 231,936 DIGBLDG_tlog_200406070445.TRN
06/07/2004 05:15 AM 297,472 DIGBLDG_tlog_200406070515.TRN
06/07/2004 08:15 AM 6,857,216 DIGBLDG_tlog_200406070815.TRN
06/07/2004 10:45 AM 25,410,048 DIGBLDG_tlog_200406071045.TRN
06/07/2004 11:15 AM 231,936 DIGBLDG_tlog_200406071115.TRN
06/07/2004 11:45 AM 231,936 DIGBLDG_tlog_200406071145.TRN
06/07/2004 12:00 PM 231,936 DIGBLDG_tlog_200406071200.TRN
06/07/2004 12:15 PM 231,936 DIGBLDG_tlog_200406071215.TRN
06/07/2004 01:45 PM 231,936 DIGBLDG_tlog_200406071345.TRN
06/07/2004 02:00 PM 231,936 DIGBLDG_tlog_200406071400.TRN
06/07/2004 05:30 PM 16,035,328 DIGBLDG_tlog_200406071730.TRN
06/07/2004 06:00 PM 758,272 DIGBLDG_tlog_200406071800.TRN
06/07/2004 06:15 PM 231,936 DIGBLDG_tlog_200406071815.TRN
06/09/2004 01:45 AM 56,492,544 DIGBLDG_tlog_200406090145.TRN
06/09/2004 02:00 AM 231,936 DIGBLDG_tlog_200406090200.TRN
06/09/2004 02:15 AM 231,936 DIGBLDG_tlog_200406090215.TRN
06/09/2004 02:30 AM 231,936 DIGBLDG_tlog_200406090230.TRN
06/09/2004 03:15 AM 231,936 DIGBLDG_tlog_200406090315.TRN
06/09/2004 03:30 AM 231,936 DIGBLDG_tlog_200406090330.TRN
06/09/2004 03:45 AM 231,936 DIGBLDG_tlog_200406090345.TRN
06/09/2004 04:00 AM 231,936 DIGBLDG_tlog_200406090400.TRN
06/09/2004 04:15 AM 231,936 DIGBLDG_tlog_200406090415.TRN
06/09/2004 04:45 AM 231,936 DIGBLDG_tlog_200406090445.TRN
06/09/2004 05:15 AM 231,936 DIGBLDG_tlog_200406090515.TRN
06/09/2004 05:30 AM 231,936 DIGBLDG_tlog_200406090530.TRN
06/09/2004 08:00 AM 231,936 DIGBLDG_tlog_200406090800.TRN
06/09/2004 12:00 PM 231,936 DIGBLDG_tlog_200406091200.TRN
06/09/2004 12:15 PM 231,936 DIGBLDG_tlog_200406091215.TRN
06/09/2004 12:30 PM 231,936 DIGBLDG_tlog_200406091230.TRN
06/09/2004 01:00 PM 231,936 DIGBLDG_tlog_200406091300.TRN
06/09/2004 01:30 PM 231,936 DIGBLDG_tlog_200406091330.TRN
06/09/2004 02:00 PM 231,936 DIGBLDG_tlog_200406091400.TRN
06/09/2004 02:15 PM 231,936 DIGBLDG_tlog_200406091415.TRN
06/09/2004 02:30 PM 231,936 DIGBLDG_tlog_200406091430.TRN
06/09/2004 07:45 PM 21,486,080 DIGBLDG_tlog_200406091945.TRN
06/09/2004 08:00 PM 231,936 DIGBLDG_tlog_200406092000.TRN
06/09/2004 08:45 PM 363,008 DIGBLDG_tlog_200406092045.TRN
06/09/2004 09:00 PM 1,613,312 DIGBLDG_tlog_200406092100.TRN
06/11/2004 05:45 AM 43,837,952 DIGBLDG_tlog_200406110545.TRN
06/11/2004 06:00 AM 231,936 DIGBLDG_tlog_200406110600.TRN
06/11/2004 06:45 AM 297,472 DIGBLDG_tlog_200406110645.TRN
06/11/2004 07:00 AM 231,936 DIGBLDG_tlog_200406110700.TRN
06/11/2004 07:15 AM 231,936 DIGBLDG_tlog_200406110715.TRN
06/11/2004 07:30 AM 758,272 DIGBLDG_tlog_200406110730.TRN
06/11/2004 10:30 AM 1,219,072 DIGBLDG_tlog_200406111030.TRN
06/11/2004 10:45 AM 231,936 DIGBLDG_tlog_200406111045.TRN
06/11/2004 11:15 AM 231,936 DIGBLDG_tlog_200406111115.TRN
06/11/2004 11:30 AM 231,936 DIGBLDG_tlog_200406111130.TRN
06/11/2004 11:45 AM 231,936 DIGBLDG_tlog_200406111145.TRN
06/11/2004 12:00 PM 231,936 DIGBLDG_tlog_200406111200.TRN
06/11/2004 12:15 PM 231,936 DIGBLDG_tlog_200406111215.TRN
06/11/2004 12:45 PM 231,936 DIGBLDG_tlog_200406111245.TRN
06/11/2004 02:45 PM 364,032 DIGBLDG_tlog_200406111445.TRN
06/11/2004 06:25 PM 0 test.txt
124 File(s) 228,552,192 bytes
2 Dir(s) 128,738,275,328 bytes free

---

BTW, check the recovery model for involved databases on Primary and Standby servers. What I would do if you need to find a workaround:
1) Take a transaction log backup
2) Switch to simple recovery mode
3) Run dbcc#%92s against involved databases
4) Switch to full recovery mode
5) Take a full backup and restore on Standby server
6) Start your transaction log backup schedule. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Are these steps considered ‘checking the recovery model’ or do I need to do something else in addition to the below? Please verify that my assumptions are correct: 1) Take a transaction log backup (Source Server)
2) Switch to simple recovery mode (Source Server)
3) Run dbcc#%92s against involved databases (Source Server)
4) Switch to full recovery mode (Source Server)
5) Take a full backup (Source Server) and restore on Standby server.
6) Start your transaction log backup schedule. In step 3, which dbcc command should I run and how do I determine if problems exist? Seems the deeper we get into this, the more I think we need a DBA! Thanks!

---

On the step3 if any issues with the database, that will be reported instantly and if you find anything suspicious post them here. And you better get hands-on DBA dutieis by referring to the articles in this site. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

Satya/Argyle- I finally got it working. I copied the dattabase backup over to the secondary server during off hours so it only took about an hour instead of 3. Then, I set up log shipping. Since the databases were only about an hour out of sync, it was able to sync up sucessfully. It seems as though our limited bandwidth does not want to synch up if the copy of the database is more than a few hours old. Thanks for all you advice! We’re looking into updating our pipe to the secondary office soon. -Patrick

---

]]>