0

i am facing issue in sql server Transactional replication and not able to get the root cause for it. First, let me tell you that i am not a DBA, so i may be dumb on few DBA concepts. i am .Net developer and i have been given responsibility to setup the replication.

i have a Database in Headoffice and replicating few Tables to another server at retail Store. First time, i configured the replication with selected articles. the replication was continuous. it was running fine, but one Sunday night, it got failed with error "process could not execute 'sp_replcmds'". after spending sometime on google, i couldn't find any solution. so, i rebuilt the Replication, but this time the replication was scheduled (every 15 Min), also i configured it as PULL instead of PUSH. it started, but again next Sunday night it got crashed. So, i analyzed that in Sunday night, i had configured the Reindexing Job on the database, and Since, the recovery model was full, it was generating a very large TLOG and Repolication agent was not able to parse that. Now, the third time, i again Rebuilt the Replication, and this time i scheduled the replication every 15 minutes but from 8:00 AM Morning to 11:30 PM, because after 11:30, no store do any transaction. Also, for Reindexing Job, i added 2 more steps. before Re-Indexing, i was changing the recovery model to simple and then Re-Indexing and after that i was changing the Recovery model back to Full. i was changing the recovery to Full, irrespective of the result from Re-Indexing step. This setup was working fine and worked properly for around 2 Months.

Now, after 2 Months, again one Sunday night it got failed, with the same reason ("process could not execute 'sp_replcmds'"). Actually, i had scheduled the backup job, and i was taking Full Backup everyday and Log backup every 15 minutes, and no differential backup. after, discovering that i had not configured the differential backup, i also configured the same (every 6 Hours). but, after configuring the Differential backup, in Sunday night Replication got failed.

Now, anybody, please help me with the recommended setup for my scenario. my setup is sql server - SQL Server 2008 R2 Enterprise on Windows Server 2008 R2 Distributor and Publisher are on same machine. Subsriber is on the Retail Store server.

Vineet Jaiswal
  • 31
  • 1
  • 6

1 Answers1

0

sp_replcmds is run by the log reader agent against the published database to get, well, replicated commands. According to the documentation, one needs to be at least db_owner to run that command. Make sure whatever account is running the log reader agent has at least db_owner in the published database.

Ben Thul
  • 31,080
  • 4
  • 45
  • 68
  • HI Ben, Thanks for reply. i have already mentioned that the replication was running fine. it got broke after enabling the Differential backup. i am not sure, if Differential backup is the only reason. in my setup, i have combination of Backup(Full,Diff and Tlog) , Replication(PULL) and Re-Indexing(Once a week, Full DB). which is very common. Also, i am changing the Recovery mode for skipping the heavy log while Re-Indexing. Please suggest, what should be the recommended way of setting up these. – Vineet Jaiswal May 22 '14 at 03:37
  • So it sounds like replication works for a while but then breaks. The next thing I would check is the 'sync with backup' option at both the published and distributor database. You can do this with `select databasepropertyex('yourDbName', 'IsSyncWithBackup'`. My theory is that you have it turned on at the distributor and that's preventing the log from clearing at the publisher. When replication fails, is the drive that houses the log file for the publisher full? – Ben Thul May 22 '14 at 11:56
  • 1. Full recovery model is ok if you have frequent t-log backups. 2 Diff : not sure if you need it ? 3. Not sure which script to mainatin index you are using. But this one is nice http://ola.hallengren.com/ 4. This re-indexing with switch recovery model sound scary. Don;t do this. 5. Only log reader is crashing ? On subscriber everything is ok ? – grillazz May 22 '14 at 12:18