Frequently Raised Errors in Log-Shipping

1. Question : IS it possible to log ship database between SQL 2000 & SQL 2008?

 Answer: No, thats impossible, In SQL 2008 transaction log architecture is changed compared to SQL 2000 and hence you won’t be able to restore tlog backups from SQL 2000 to SQL 2008 or vice versa.

2. Question:I’m getting the below error message in restoration job on secondary server, WHY?

[Microsoft SQL-DMO (ODBC SQLState: 42000)]
Error 4305: [Microsoft][ODBC SQL Server Driver][SQL Server]The log in this backup set begins at LSN 7000000026200001, which is too late to apply to the database. An earlier log backup that includes LSN 6000000015100001 can be restored.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally.

Answer: Was your sql server or agent restarted Y’day in either source or destination ? because the error states there is a mismatch in LSN. A particular tran log was not applied in the destination server hence the subsequent tran logs cannot be applied as a result !

You can check log shipping monitor \ log shipping tables to check the which transaction log is last applied to secondary db, if the next consecutive transaction logs are available in the secondary server share folder you manually RESTORE the logs with NORECOVERY option, Once you restored all the logs automatically from the next cycle the job will work fine.

Incase if you are not able to find the next transaction log in secondary server shared folder, you need to reconfigure log shipping. Try the below tasks to re-establish log shipping again.

• Disable all the log shipping jobs in source and destination servers
• Take a full backup in source and restore it in secondary server using the With Standby option
• Enable all the jobs you disabled previously in step1

3. Question: Is it possible load balance in log shipping?

Answer: Yes of course it’s possible in log shipping, while configuring log shipping you have the option to choose standby or no recovery mode, and there you select STANDBY option to make the secondary database read-only.

4. Question: Can I take full backup of the log shipped database in primary server??

Answer: In SQL Server 2000 you won’t be able to take full backup of log shipped database, because this will break the LSN chain and it directly affects the log shipping.
In SQL Server 2008, yes its possible. You can take full backup of log shipped database and this won’t affect the log shipping.

5. Question : Can I shrink log shipped database log file??

Answer: Yes of course you can shrink the log file, but you shouldn’t use WITH TRUNCATE option. If you use this option obviously log shipping will be disturbed.

6. Can I take full backup of the log shipped database in secondary server?

Answer: No chance, you won’t be able to execute BACKUP command against a log shipped database in secondary server.

7. I’ve configured Log shipping successfully on standby mode, but in the restoration job I’m getting the below error. What I do to avoid this in future??
Message
2006-07-31 09:40:54.33 *** Error: Could not apply log backup file ‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\LogShip\TEST_20060731131501.trn’ to secondary database ‘TEST’.(Microsoft.SqlServer.Management.LogShipping) ***
2006-07-31 09:40:54.33 *** Error: Exclusive access could not be obtained because the database is in use.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***

Answer: To restore transaction logs to the secondary db, SQL Server needs exclussive access on the database. When you configure it in standby mode, users will be able to access the database and runs query against the secondary db. Hence If the scheduled restore jobs runs at that time, the db will have a lock and it won’t allow SQL Server to restore the tlogs. To avoid this you need to check “Disconnect users in the database when restoring backups” options in log shipping configuration wizard.

8. Question : Suddenly I’m getting the error below, How can I rectify this?
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 4323: [Microsoft][ODBC SQL Server Driver][SQL Server]The database is marked suspect. Transaction logs cannot be restored. Use RESTORE DATABASE to recover the database.
[Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally

Answer : We had the same issue some time ago, this was related to a new file being created in a filegroup on the source. Don’t know if this applies to your case, but restoring a backup of this new file on the secondary server solved the problem.

9. Question : Is it possible to log ship database from SQL server 2005 to SQL server 2008 and vice versa?

Answer : Yes you can log ship database from SQL server 2005 to SQL Server 2008 this will work. However log shipping from SQL Server 2008 to SQL Server 2005 is not possible because you won’t be able to restore SQL server 2008 backup to SQL Server 2005 (downgrading version)

1. Error message 14420 and error message 14421 that occur when you use log shipping:

Error message 14420 Error: 14420, Severity: 16, State: 1
The log shipping destination %s.%s is out of sync by %s minutes.

Error message 14421 Error: 14421, Severity: 16, State: 1
The log shipping destination %s.%s is out of sync by %s minutes.

If you are using SQL Server 2008, the description for these error messages are different:

Error message 14420 Error: 14420, Severity: 16, State: 1
The log shipping primary database %s.%s has backup threshold of %d minutes and has not performed a backup log operation for %d minutes. Check agent log and logshipping monitor information.

Error message 14421 Error: 14421, Severity: 16, State: 1
The log shipping secondary database %s.%s has restore threshold of %d minutes and is out of sync. No restore was performed for %d minutes. Restored latency is %d minutes. Check agent log and logshipping monitor information.