Log shipping skip trx log and not able to resotre trx backup | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Log shipping skip trx log and not able to resotre trx backup

Hi,
i have restore the full backup into secondary server and manual run the script to configure the log shipping setting. i also have copy the trx backup from primary server into secondary server. when i try to run the schedule copy job, the job run successful but no file are copy. it is same to restore job. below is the process which i copy from hisotry
Message
2010-04-03 11:42:26.84 Skipped log backup file. Secondary DB: ‘LS_Account’, File: ‘d:LogShippingAccount_20100402003001.trn’
2010-04-03 11:42:26.90 Skipped log backup file. Secondary DB: ‘LS_Account’, File: ‘d:LogShippingAccount_20100402004501.trn’
2010-04-03 11:42:27.00 Skipped log backup file. Secondary DB: ‘LS_Account’, File: ‘d:LogShippingAccount_20100402010000.trn’
2010-04-03 11:42:27.12 Skipped log backup file. Secondary DB: ‘LS_Account’, File: ‘d:LogShippingAccount_20100402011500.trn’
2010-04-03 11:42:27.73 Skipped log backup file. Secondary DB: ‘LS_Account’, File: ‘d:LogShippingAccount_20100402013000.trn’
2010-04-03 11:42:27.82 Found first log backup file to restore. Secondary DB: ‘LS_Account’, File: ‘d:LogShippingAccount_20100402014500.trn’
2010-04-03 11:42:27.82 The restore operation was successful. Secondary Database: ‘LS_Account’, Number of log backup files restored: 0
2010-04-03 11:42:27.84 Deleting old log backup files. Primary Database: ‘Account’
2010-04-03 11:42:27.85 The restore operation was successful. Secondary ID: ‘9cbeb51a-5dfe-4295-a68d-c347e3f73c95’
i have other 2 database ( CRM, Payroll ) which is doing the same but only this database i face this problem. CRM and Payroll Db now is running without any problem. Please help !

It doesn’t make any send to work with one db not the other…other than there is some config change or there is some other tlog backup job on primary running.

i have check, all these 3 db log shipping setting are same ( other than database name ). and also confirm there is only 1 trx backup job in primary server.
i have download all the trx backup file from primary server back to secondary sever and i manage to retore them 1 by 1 up to date. this means the trn backup should not have any problem with the database. the problem i am facing is why the SQL server manage to locate the trx backup file but not restore them.
refer to the SQL message, ‘2010-04-03 11:42:27.82 Found first log backup file to restore’, but sql server did not perform the restore job and report successful ( 2010-04-03 11:42:27.82 The restore operation was successful ). but you can see there is no file to restore ( Number of log backup files restored: 0 )
this is the CRM log shipping message which was successful
Message
2010-04-06 09:45:05.62 Retrieving restore settings. Secondary ID: ‘fa2bb71d-7fba-4d97-bb18-9e45870edaba’
2010-04-06 09:45:05.64 Retrieved common restore settings. Primary Server: ‘Production’, Primary Database: ‘CRM’, Backup Destination Directory: ‘d:LogShipping’, File Retention Period: 4320 minute(s)
2010-04-06 09:45:05.64 Retrieved database restore settings. Secondary Database: ‘LS_CRM’, Restore Delay: 5, Restore All: True, Restore Mode: Standby, Disconnect Users: True, Last Restored File: d:LogShippingCRM_20100406010001.trn, Block Size: Not Specified, Buffer Count: Not Specified, Max Transfer Size: Not Specified
2010-04-06 09:45:06.81 Disconnecting users. Secondary DB: ‘LS_CRM’
2010-04-06 09:45:14.48 Restored log backup file. Secondary DB: ‘LS_CRM’, File: ‘d:LogShippingCRM_20100406011500.trn’
2010-04-06 09:45:15.06 The restore operation was successful. Secondary Database: ‘LS_CRM’, Number of log backup files restored: 1
2010-04-06 09:45:15.09 Deleting old log backup files. Primary Database: ‘CRM’
2010-04-06 09:45:15.15 Deleting log backup file. File: ‘d:LogShippingCRM_20100403013000.trn’, Last Write Time (UTC): ‘4/3/2010 1:30:00 AM’
2010-04-06 09:45:15.20 The restore operation was successful. Secondary ID: ‘fa2bb71d-7fba-4d97-bb18-9e45870edaba’
2010-04-06 09:45:15.32 —– END OF TRANSACTION LOG RESTORE —–
Please help !!!!

i have search on the internet and also found some user facing the same problem. notice that if sql return this kind of message ‘Skipped log backup file. Secondary …..’ then the log shipping will not work. unfortunately all the result i search on the internet there is no answer for it.
please help ….. if the log shipping not wrok for this db, i might need to study how to configure this db using database mirroring….

Did you try to cleaning the log shipping for this db and redo it again…see it resolves…

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |