December 31, 2010 at 3:32 pm
Hi,
I have built a peer to peer transactional replication on sql server 2005 enterprise edition (SP2) between two servers (JC-DB-PRI and JC-DB-BCK) and its worked fine for 6 months, After that I've added a job to re-indexing tables and its worked for a while then the replication stopped for a one day then its worked again for a one month, now the replication stopped for 4 days and LogReader agent gives the following error:
The process could not execute 'sp_replcmds' on 'server_name'
I have created a new LogReader agent profile with QueryTimeOut = 2200 and ReadBatchSize = 1 and I still get the same error and the status of LogReader agent is 'Between Retries'
here's the log of LogReader agent job:
2010-12-30 15:56:28.390 Microsoft SQL Server Log Reader Agent 9.00.1399.06
2010-12-30 15:56:28.406 Copyright (c) 2000 Microsoft Corporation
2010-12-30 15:56:28.406 Microsoft SQL Server Replication Agent: logread
2010-12-30 15:56:28.406
2010-12-30 15:56:28.406 The timestamps prepended to the output lines are expressed in terms of UTC time.
2010-12-30 15:56:28.406 User-specified agent parameter values:
-Publisher JC-DB-PRI
-PublisherDB ETS
-Distributor JC-DB-PRI
-DistributorSecurityMode 1
-output c:\logreader.txt
-outputverboselevel 3
-Continuous
-XJOBID 0x1BDF83D170EC864A9B8C0AF408C578D2
-XJOBNAME JC-DB-PRI-ETS-29
-XSTEPID 2
-XSUBSYSTEM LogReader
-XSERVER JC-DB-PRI
-XCMDLINE 0
-XCancelEventHandle 000007D4
2010-12-30 15:56:28.656 Connecting to OLE DB JC-DB-PRI at datasource: 'JC-DB-PRI', location: '', catalog: 'ETS', providerstring: '' using provider 'SQLNCLI'
2010-12-30 15:56:28.656 OLE DB JC-DB-PRI: JC-DB-PRI
DBMS: Microsoft SQL Server
Version: 09.00.1399
catalog name: ETS
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2010-12-30 15:56:28.656 OLE DB JC-DB-PRI 'JC-DB-PRI': select is_srvrolemember('sysadmin'), is_member ('db_owner')
2010-12-30 15:56:28.656 OLE DB JC-DB-PRI 'JC-DB-PRI': select db_id()
2010-12-30 15:56:28.656 Disconnecting from OLE DB JC-DB-PRI 'JC-DB-PRI'
2010-12-30 15:56:28.656 Parameter values obtained from agent profile:
-pollinginterval 5000
-historyverboselevel 1
-logintimeout 15
-querytimeout 2200
-readbatchsize 1
-readbatchsize 500000
2010-12-30 15:56:28.656 Connecting to OLE DB Publisher at datasource: 'JC-DB-PRI', location: '', catalog: 'ETS', providerstring: '' using provider 'SQLNCLI'
2010-12-30 15:56:28.656 OLE DB Publisher: JC-DB-PRI
DBMS: Microsoft SQL Server
Version: 09.00.1399
catalog name: ETS
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2010-12-30 15:56:28.656 OLE DB Publisher: JC-DB-PRI
DBMS: Microsoft SQL Server
Version: 09.00.1399
catalog name: ETS
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2010-12-30 15:56:28.671 OLE DB Publisher 'JC-DB-PRI': select @@SERVERNAME
2010-12-30 15:56:28.671 Connecting to OLE DB DISTOLE at datasource: 'JC-DB-PRI', location: '', catalog: 'distribution', providerstring: '' using provider 'SQLNCLI'
2010-12-30 15:56:28.671 OLE DB DISTOLE: JC-DB-PRI
DBMS: Microsoft SQL Server
Version: 09.00.1399
catalog name: distribution
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2010-12-30 15:56:28.671 OLE DB DISTOLE: JC-DB-PRI
DBMS: Microsoft SQL Server
Version: 09.00.1399
catalog name: distribution
user name: dbo
API conformance: 0
SQL conformance: 0
transaction capable: 1
read only: F
identifier quote char: "
non_nullable_columns: 0
owner usage: 15
max table name len: 128
max column name len: 128
need long data len:
max columns in table: 1000
max columns in index: 16
max char literal len: 131072
max statement len: 131072
max row size: 131072
2010-12-30 15:56:28.671 OLE DB DISTOLE 'JC-DB-PRI': select datasource, srvid from master..sysservers where upper(srvname) = upper(N'JC-DB-PRI')
2010-12-30 15:56:28.671 OLE DB Publisher 'JC-DB-PRI': sp_MSgetversion
2010-12-30 15:56:28.671 Status: 4096, code: 20024, text: 'Initializing'.
2010-12-30 15:56:28.671 The agent is running. Use Replication Monitor to view the details of this agent session.
2010-12-30 15:56:28.671 OLE DB DISTOLE 'JC-DB-PRI': sp_MSget_last_transaction @publisher_id = 0, @publisher_db = N'ETS', @for_truncate = 0x0
2010-12-30 15:56:28.671 OLE DB DISTOLE 'JC-DB-PRI': sp_MSquery_syncstates 0, N'ETS'
2010-12-30 15:56:28.671 OLE DB DISTOLE 'JC-DB-PRI': sp_MSget_last_transaction @publisher_id = 0, @publisher_db = N'ETS', @for_truncate = 0x1
2010-12-30 15:56:28.671 Publisher: {call sp_repldone ( 0x017c83e4000001280003, 0x017c83e4000001280003, 0, 0)}
2010-12-30 15:56:28.671 Publisher: {call sp_replcmds (1, 0, 0, , 0, 500000)}
2010-12-30 15:56:29.703 Status: 0, code: 20011, text: 'The process could not execute 'sp_replcmds' on 'JC-DB-PRI'.'.
2010-12-30 15:56:29.703 The process could not execute 'sp_replcmds' on 'JC-DB-PRI'.
2010-12-30 15:56:29.703 Status: 0, code: 0, text: 'Unspecified error'.
2010-12-30 15:56:29.703 Status: 0, code: 22037, text: 'The process could not execute 'sp_replcmds' on 'JC-DB-PRI'.'.
2010-12-30 15:56:29.718 Disconnecting from OLE DB DISTOLE 'JC-DB-PRI'
2010-12-30 15:56:29.750 Disconnecting from OLE DB Publisher 'JC-DB-PRI'
Need help on this please !!!
January 4, 2011 at 2:27 am
Ok
It was solved by restarting the server. As i read in this link
but i have a question on re-indexing tables job, Is it ok to execute it every day or is there any optimization tips to do reduce the load on the server because the status of replication performance is critical !!! (maybe because the huge transactions that are not replicated in the past 3 days.) ?!!!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply