January 21, 2019 at 1:47 pm
Hi Team,
I have an issue with replication, the log reader always gives this error:
The process could not execute 'sp_MSadd_replcmds'
The database publisher, is restored from a backup file from production, and the subscription database is also restored from a database backup from production.
Now when I try to add the publisher, it adds successfully. Only the log reader agent gives me this error. And after adding the subscriber the replication monitor shows me the same error.
The only solution and command that I found so far that helped me is to run : sp_replrestart, but again when we do a fresh restore of the database both publisher and subscriber and then recreate replication the error again happens.
Any idea and suggestions highly appreciated
January 21, 2019 at 5:25 pm
Tomys - Monday, January 21, 2019 1:47 PMHi Team,I have an issue with replication, the log reader always gives this error:
The process could not execute 'sp_MSadd_replcmds'The database publisher, is restored from a backup file from production, and the subscription database is also restored from a database backup from production.
Now when I try to add the publisher, it adds successfully. Only the log reader agent gives me this error. And after adding the subscriber the replication monitor shows me the same error.
The only solution and command that I found so far that helped me is to run : sp_replrestart, but again when we do a fresh restore of the database both publisher and subscriber and then recreate replication the error again happens.
Any idea and suggestions highly appreciated
With backup and restore with replication, that is usually one of the steps - executing sp_replrestart to get things synchronized.
Strategies for Backing Up and Restoring Snapshot and Transactional Replication
Sue
January 21, 2019 at 5:41 pm
Sue_H - Monday, January 21, 2019 5:25 PMTomys - Monday, January 21, 2019 1:47 PMHi Team,I have an issue with replication, the log reader always gives this error:
The process could not execute 'sp_MSadd_replcmds'The database publisher, is restored from a backup file from production, and the subscription database is also restored from a database backup from production.
Now when I try to add the publisher, it adds successfully. Only the log reader agent gives me this error. And after adding the subscriber the replication monitor shows me the same error.
The only solution and command that I found so far that helped me is to run : sp_replrestart, but again when we do a fresh restore of the database both publisher and subscriber and then recreate replication the error again happens.
Any idea and suggestions highly appreciated
With backup and restore with replication, that is usually one of the steps - executing sp_replrestart to get things synchronized.
Strategies for Backing Up and Restoring Snapshot and Transactional ReplicationSue
thank you , I found that if I create a database with another name and restore it with same backup file that I used for the original, and then start replication it works with out any issue(no erro and no need to run sp_replrestart)
So is the database name causing issue? I ran sp_removedbreplication too, not sure what next to do here, without adding the sp_replrestart.
The job we have is
1. Clean up replication
2. Restore the database using the latest backup file from a shared location one to the publisher and another backup file to the subscriber 3. Recreate replication
Please let me know any ideas.
January 22, 2019 at 8:21 am
Tomys - Monday, January 21, 2019 5:41 PMSue_H - Monday, January 21, 2019 5:25 PMTomys - Monday, January 21, 2019 1:47 PMHi Team,I have an issue with replication, the log reader always gives this error:
The process could not execute 'sp_MSadd_replcmds'The database publisher, is restored from a backup file from production, and the subscription database is also restored from a database backup from production.
Now when I try to add the publisher, it adds successfully. Only the log reader agent gives me this error. And after adding the subscriber the replication monitor shows me the same error.
The only solution and command that I found so far that helped me is to run : sp_replrestart, but again when we do a fresh restore of the database both publisher and subscriber and then recreate replication the error again happens.
Any idea and suggestions highly appreciated
With backup and restore with replication, that is usually one of the steps - executing sp_replrestart to get things synchronized.
Strategies for Backing Up and Restoring Snapshot and Transactional ReplicationSue
thank you , I found that if I create a database with another name and restore it with same backup file that I used for the original, and then start replication it works with out any issue(no erro and no need to run sp_replrestart)
So is the database name causing issue? I ran sp_removedbreplication too, not sure what next to do here, without adding the sp_replrestart.
The job we have is
1. Clean up replication
2. Restore the database using the latest backup file from a shared location one to the publisher and another backup file to the subscriber 3. Recreate replicationPlease let me know any ideas.
Not directly the name but it would be seen as a new replication component. It's hard to guess at all of the settings that come into play but I would suspect a lot of the issues are related to one of the other databases. The piece you never mentioned and seem to be leaving out of all of this is the distribution database. Publications and subscriptions are in there by database name as well as other replication settings.
It's good to remember that sp_removereplication doesn't touch the distributor. And sp_replrestart synchronizes data between the publisher and the distributor.
So it plays an important role in all of this.I have no idea what if any changes you are doing or what configurations you have with the distributor but if you are attempting to start totally clear of any and all replication after the restores, you would also need to remove the distributor setup. Since a lot of the time, that's not needed on restores - especially in production - sp_replirestart is there to resynchronize the data.
Sue
January 22, 2019 at 9:00 am
Sue_H - Tuesday, January 22, 2019 8:21 AMTomys - Monday, January 21, 2019 5:41 PMSue_H - Monday, January 21, 2019 5:25 PMTomys - Monday, January 21, 2019 1:47 PMHi Team,I have an issue with replication, the log reader always gives this error:
The process could not execute 'sp_MSadd_replcmds'The database publisher, is restored from a backup file from production, and the subscription database is also restored from a database backup from production.
Now when I try to add the publisher, it adds successfully. Only the log reader agent gives me this error. And after adding the subscriber the replication monitor shows me the same error.
The only solution and command that I found so far that helped me is to run : sp_replrestart, but again when we do a fresh restore of the database both publisher and subscriber and then recreate replication the error again happens.
Any idea and suggestions highly appreciated
With backup and restore with replication, that is usually one of the steps - executing sp_replrestart to get things synchronized.
Strategies for Backing Up and Restoring Snapshot and Transactional ReplicationSue
thank you , I found that if I create a database with another name and restore it with same backup file that I used for the original, and then start replication it works with out any issue(no erro and no need to run sp_replrestart)
So is the database name causing issue? I ran sp_removedbreplication too, not sure what next to do here, without adding the sp_replrestart.
The job we have is
1. Clean up replication
2. Restore the database using the latest backup file from a shared location one to the publisher and another backup file to the subscriber 3. Recreate replicationPlease let me know any ideas.
Not directly the name but it would be seen as a new replication component. It's hard to guess at all of the settings that come into play but I would suspect a lot of the issues are related to one of the other databases. The piece you never mentioned and seem to be leaving out of all of this is the distribution database. Publications and subscriptions are in there by database name as well as other replication settings.
It's good to remember that sp_removereplication doesn't touch the distributor. And sp_replrestart synchronizes data between the publisher and the distributor.
So it plays an important role in all of this.I have no idea what if any changes you are doing or what configurations you have with the distributor but if you are attempting to start totally clear of any and all replication after the restores, you would also need to remove the distributor setup. Since a lot of the time, that's not needed on restores - especially in production - sp_replirestart is there to resynchronize the data.Sue
Thank you so much,
Next, I also want to add whenever the backup is restored on to this particular server, I see in sql server error log, the following message
The log scan number (2812219:19703:5) passed to log scan in database 'xx' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.
DBCC checkdb didnt return any errors
And the whole process I tried on a different server, with same backup and setting up replication no issues seen..
January 22, 2019 at 11:28 am
Tomys - Tuesday, January 22, 2019 9:00 AMSue_H - Tuesday, January 22, 2019 8:21 AMTomys - Monday, January 21, 2019 5:41 PMSue_H - Monday, January 21, 2019 5:25 PMTomys - Monday, January 21, 2019 1:47 PMHi Team,I have an issue with replication, the log reader always gives this error:
The process could not execute 'sp_MSadd_replcmds'The database publisher, is restored from a backup file from production, and the subscription database is also restored from a database backup from production.
Now when I try to add the publisher, it adds successfully. Only the log reader agent gives me this error. And after adding the subscriber the replication monitor shows me the same error.
The only solution and command that I found so far that helped me is to run : sp_replrestart, but again when we do a fresh restore of the database both publisher and subscriber and then recreate replication the error again happens.
Any idea and suggestions highly appreciated
With backup and restore with replication, that is usually one of the steps - executing sp_replrestart to get things synchronized.
Strategies for Backing Up and Restoring Snapshot and Transactional ReplicationSue
thank you , I found that if I create a database with another name and restore it with same backup file that I used for the original, and then start replication it works with out any issue(no erro and no need to run sp_replrestart)
So is the database name causing issue? I ran sp_removedbreplication too, not sure what next to do here, without adding the sp_replrestart.
The job we have is
1. Clean up replication
2. Restore the database using the latest backup file from a shared location one to the publisher and another backup file to the subscriber 3. Recreate replicationPlease let me know any ideas.
Not directly the name but it would be seen as a new replication component. It's hard to guess at all of the settings that come into play but I would suspect a lot of the issues are related to one of the other databases. The piece you never mentioned and seem to be leaving out of all of this is the distribution database. Publications and subscriptions are in there by database name as well as other replication settings.
It's good to remember that sp_removereplication doesn't touch the distributor. And sp_replrestart synchronizes data between the publisher and the distributor.
So it plays an important role in all of this.I have no idea what if any changes you are doing or what configurations you have with the distributor but if you are attempting to start totally clear of any and all replication after the restores, you would also need to remove the distributor setup. Since a lot of the time, that's not needed on restores - especially in production - sp_replirestart is there to resynchronize the data.Sue
Thank you so much,
Next, I also want to add whenever the backup is restored on to this particular server, I see in sql server error log, the following message
The log scan number (2812219:19703:5) passed to log scan in database 'xx' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.DBCC checkdb didnt return any errors
And the whole process I tried on a different server, with same backup and setting up replication no issues seen..
From what I can tell with your process, a different server would have a different distribution database. The distribution database has the commands that need to be replicated (as well as meta data and history). That last error is usually when the last LSN in Transaction Log is less than what the Log Reader is trying to find..
It could be that a lot of this is from needing to understand how all the pieces between distribution, publication and subscription tie in with each other. You may want to go back through that article I first linked to as it does address considerations when restoring. And executing sp_replrestart is usually used when restoring replicated databases. So avoiding the use of that really is not really necessary.There are a lot of other replication settings that come into play - sync with backup being one of the many the impact the whole process. That article addresses some of the settings as well.
Sue
January 22, 2019 at 11:41 am
Sue_H - Tuesday, January 22, 2019 11:28 AMTomys - Tuesday, January 22, 2019 9:00 AMSue_H - Tuesday, January 22, 2019 8:21 AMTomys - Monday, January 21, 2019 5:41 PMSue_H - Monday, January 21, 2019 5:25 PMTomys - Monday, January 21, 2019 1:47 PMHi Team,I have an issue with replication, the log reader always gives this error:
The process could not execute 'sp_MSadd_replcmds'The database publisher, is restored from a backup file from production, and the subscription database is also restored from a database backup from production.
Now when I try to add the publisher, it adds successfully. Only the log reader agent gives me this error. And after adding the subscriber the replication monitor shows me the same error.
The only solution and command that I found so far that helped me is to run : sp_replrestart, but again when we do a fresh restore of the database both publisher and subscriber and then recreate replication the error again happens.
Any idea and suggestions highly appreciated
With backup and restore with replication, that is usually one of the steps - executing sp_replrestart to get things synchronized.
Strategies for Backing Up and Restoring Snapshot and Transactional ReplicationSue
thank you , I found that if I create a database with another name and restore it with same backup file that I used for the original, and then start replication it works with out any issue(no erro and no need to run sp_replrestart)
So is the database name causing issue? I ran sp_removedbreplication too, not sure what next to do here, without adding the sp_replrestart.
The job we have is
1. Clean up replication
2. Restore the database using the latest backup file from a shared location one to the publisher and another backup file to the subscriber 3. Recreate replicationPlease let me know any ideas.
Not directly the name but it would be seen as a new replication component. It's hard to guess at all of the settings that come into play but I would suspect a lot of the issues are related to one of the other databases. The piece you never mentioned and seem to be leaving out of all of this is the distribution database. Publications and subscriptions are in there by database name as well as other replication settings.
It's good to remember that sp_removereplication doesn't touch the distributor. And sp_replrestart synchronizes data between the publisher and the distributor.
So it plays an important role in all of this.I have no idea what if any changes you are doing or what configurations you have with the distributor but if you are attempting to start totally clear of any and all replication after the restores, you would also need to remove the distributor setup. Since a lot of the time, that's not needed on restores - especially in production - sp_replirestart is there to resynchronize the data.Sue
Thank you so much,
Next, I also want to add whenever the backup is restored on to this particular server, I see in sql server error log, the following message
The log scan number (2812219:19703:5) passed to log scan in database 'xx' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.DBCC checkdb didnt return any errors
And the whole process I tried on a different server, with same backup and setting up replication no issues seen..
From what I can tell with your process, a different server would have a different distribution database. The distribution database has the commands that need to be replicated (as well as meta data and history). That last error is usually when the last LSN in Transaction Log is less than what the Log Reader is trying to find..
It could be that a lot of this is from needing to understand how all the pieces between distribution, publication and subscription tie in with each other. You may want to go back through that article I first linked to as it does address considerations when restoring. And executing sp_replrestart is usually used when restoring replicated databases. So avoiding the use of that really is not really necessary.There are a lot of other replication settings that come into play - sync with backup being one of the many the impact the whole process. That article addresses some of the settings as well.Sue
thank you again, I'll check the article. I probably think this is more of an issue with this server for this particular database name that I use(but no clue what to do to fix), I even did a rebuild log file, created the db file on different drives with different names and when restoring db if the db name is the same and start replication it fails!!...
As I was saying earlier when I give a different name for the db and restore the backup and start replication all works well.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply