Error with Transactional Replication

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Error with Transactional Replication

Author

Topic

zach_john
Starting Member

3 Posts

Posted - 2003-07-07 : 10:32:39

SQL 2k

I have an Active/Active cluster that is home to my Publisher and Distributor. When failed to a single node environment all is well when seperated to a/a I get the following error on the Log reader job step #2. The step did not generate any output. The step failed.

Here's all the details,

 
I have  replication setup using 3 servers a publisher, a distributor, and a subscriber. In my case the publisher and the disributor are on seperate an active/active cluster but on seperate nodes. 
On my distributor, under rep mon, under log reader I get a failure "The step did not generate any output. The step failed." 
This does not happen right away, I can get 1-100 transactions delivered before the failure occurs, it appears to fail or stop being continous when there are no more transactions,  I can then right click on the agent and restart the job and all is well for the next few transactions. 
I added -outputverboselevel 2 - output c:\replinfo.txt to the job step for the log reader 
Here's a copy of the replinfo.txt when the job fails. 
 
Microsoft SQL Server Log Reader Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: MySTGVIRPUB01-APPORTAL-8


Connecting to Publisher 'MySTGVIRPUB01.APPORTAL'

Server: 
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288

[7/3/2003 9:22:02 AM]MySTGVIRPUB01.APPORTAL: select @@SERVERNAME
[7/3/2003 9:22:02 AM]MySTGVIRPUB01.APPORTAL: sp_MSgetversion
Status: 4096, code: 20024, text: 'Initializing'.
The agent is running. Use Replication Monitor to view the details of this agent session.
Publisher: {call sp_repldone ( 0x000ac8dc000005a6002b, 0x000ac8dc000005a6002b, 0, 0)}
Publisher: {call sp_replcmds (500, 0)}
Status: 4, code: 20051, text: 'Delivering replicated transactions'.
Status: 4, code: 6002, text: 'Committed batch to distribution database.'.
Publisher: {call sp_repldone ( NULL, 0x000ac8dc000010b70024, 0, 812)}
Publisher: exec sp_replcounters N'APPORTAL'
Status: 4, code: 22021, text: 'Batch committed.  Batch consisted of 134 commands, 133 xacts.  Last xact: 0x000ac8dc000010b70001, '0x000ac8dc000010b70024'.'.
Publisher: {call sp_replcmds (500, 0)}


********************************** STATISTICS *********************************
Execution time (ms): 23109
Work time (ms): 22578
Distribute Repl Cmds Time(ms): 828
Fetch time(ms): 735
Repldone time(ms): 32
Write time(ms): 592
   Num Trans: 133  Num Trans/Sec: 5.890690
   Num Cmds: 134  Num Cmds/Sec: 5.934981
*******************************************************************************

Disconnecting from Publisher 'MySTGVIRPUB01'

I dont see any errors. The log reader is set to (-continuous), the default setting. 

Here's a copy of it when it works, albiet the pub and the distr are now on the same server, but as two seperate instances of SQL Server.  


Microsoft SQL Server Log Reader Agent 8.00.760
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: MySTGVIRPUB01-APPORTAL-8

Connecting to Publisher 'MySTGVIRPUB01.APPORTAL'

Server: 
DBMS: Microsoft SQL Server
Version: 08.00.0760
user name: dbo
API conformance: 2
SQL conformance: 1
transaction capable: 2
read only: N
identifier quote char: "
non_nullable_columns: 1
owner usage: 31
max table name len: 128
max column name len: 128
need long data len: Y
max columns in table: 1024
max columns in index: 16
max char literal len: 524288
max statement len: 524288
max row size: 524288

[7/3/2003 9:37:08 AM]MySTGVIRPUB01.APPORTAL: select @@SERVERNAME
[7/3/2003 9:37:08 AM]MySTGVIRPUB01.APPORTAL: sp_MSgetversion
Status: 4096, code: 20024, text: 'Initializing'.
The agent is running. Use Replication Monitor to view the details of this agent session.
Publisher: {call sp_repldone ( 0x000ac8dc000010b70024, 0x000ac8dc000010b70024, 0, 0)}
Publisher: {call sp_replcmds (500, 0)}
Status: 4, code: 20051, text: 'Delivering replicated transactions'.
Status: 4, code: 20051, text: 'Delivering replicated transactions'.
Status: 4, code: 20051, text: 'Delivering replicated transactions'.
Status: 4, code: 20051, text: 'Delivering replicated transactions'.
Status: 4, code: 6002, text: 'Committed batch to distribution database.'.
Publisher: {call sp_repldone ( NULL, 0x000ac8dc000034c4003d, 0, 3922)}
Publisher: exec sp_replcounters N'APPORTAL'
Status: 4, code: 22021, text: 'Batch committed.  Batch consisted of 424 commands, 418 xacts.  Last xact: 0x000ac8dc000034c40001, '0x000ac8dc000034c4003d'.'.
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Status: 4, code: 6002, text: 'Committed batch to distribution database.'.
Publisher: {call sp_repldone ( NULL, 0x000ac8dc000035290001, 0, 375)}
Publisher: exec sp_replcounters N'APPORTAL'
Status: 4, code: 22021, text: 'Batch committed.  Batch consisted of 1 commands, 1 xacts.  Last xact: 0x000ac8dc0000350e0001, '0x000ac8dc000035290001'.'.
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Status: 4, code: 6002, text: 'Committed batch to distribution database.'.
Publisher: {call sp_repldone ( NULL, 0x000ac8dc000036e9003a, 0, 203)}
Publisher: exec sp_replcounters N'APPORTAL'
Status: 4, code: 22021, text: 'Batch committed.  Batch consisted of 9 commands, 9 xacts.  Last xact: 0x000ac8dc000036d40001, '0x000ac8dc000036e9003a'.'.
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Status: 4, code: 6002, text: 'Committed batch to distribution database.'.
Publisher: {call sp_repldone ( NULL, 0x000ac8dc00003a76003a, 0, 359)}
Publisher: exec sp_replcounters N'APPORTAL'
Status: 4, code: 22021, text: 'Batch committed.  Batch consisted of 16 commands, 16 xacts.  Last xact: 0x000ac8dc00003a760001, '0x000ac8dc00003a76003a'.'.
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Status: 4, code: 6002, text: 'Committed batch to distribution database.'.
Publisher: {call sp_repldone ( NULL, 0x000ac8dc00003aa90001, 0, 31)}
Publisher: exec sp_replcounters N'APPORTAL'
Status: 4, code: 22021, text: 'Batch committed.  Batch consisted of 1 commands, 1 xacts.  Last xact: 0x000ac8dc00003aa30001, '0x000ac8dc00003aa90001'.'.
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Status: 4, code: 6002, text: 'Committed batch to distribution database.'.
Publisher: {call sp_repldone ( NULL, 0x000ac8dc00003b78001e, 0, 125)}
Publisher: exec sp_replcounters N'APPORTAL'
Status: 4, code: 22021, text: 'Batch committed.  Batch consisted of 12 commands, 12 xacts.  Last xact: 0x000ac8dc00003b780001, '0x000ac8dc00003b78001e'.'.
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Status: 4, code: 6002, text: 'Committed batch to distribution database.'.
Publisher: {call sp_repldone ( NULL, 0x000ac8dc00003b8c0001, 0, 31)}
Publisher: exec sp_replcounters N'APPORTAL'
Status: 4, code: 22021, text: 'Batch committed.  Batch consisted of 1 commands, 1 xacts.  Last xact: 0x000ac8dc00003b860001, '0x000ac8dc00003b8c0001'.'.
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Status: 4, code: 6002, text: 'Committed batch to distribution database.'.
Publisher: {call sp_repldone ( NULL, 0x000ac8dc00003bde0017, 0, 1031)}
Publisher: exec sp_replcounters N'APPORTAL'
Status: 4, code: 22021, text: 'Batch committed.  Batch consisted of 6 commands, 6 xacts.  Last xact: 0x000ac8dc00003bbf0003, '0x000ac8dc00003bde0017'.'.
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Status: 4, code: 6002, text: 'Committed batch to distribution database.'.
Publisher: {call sp_repldone ( NULL, 0x000ac8dc00003c450001, 0, 78)}
Publisher: exec sp_replcounters N'APPORTAL'
Status: 4, code: 22021, text: 'Batch committed.  Batch consisted of 7 commands, 7 xacts.  Last xact: 0x000ac8dc00003c3e0001, '0x000ac8dc00003c450001'.'.
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Status: 4, code: 6002, text: 'Committed batch to distribution database.'.
Publisher: {call sp_repldone ( NULL, 0x000ac8dc00003c770031, 0, 406)}
Publisher: exec sp_replcounters N'APPORTAL'
Status: 4, code: 22021, text: 'Batch committed.  Batch consisted of 3 commands, 3 xacts.  Last xact: 0x000ac8dc00003c770001, '0x000ac8dc00003c770031'.'.
Publisher: {call sp_replcmds (500, 0)}
Status: 4, code: 6002, text: 'Committed batch to distribution database.'.
Publisher: {call sp_repldone ( NULL, 0x000ac8dc00003ca00027, 0, 187)}
Publisher: exec sp_replcounters N'APPORTAL'
Status: 4, code: 22021, text: 'Batch committed.  Batch consisted of 1 commands, 1 xacts.  Last xact: 0x000ac8dc00003c9f0001, '0x000ac8dc00003ca00027'.'.
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Status: 4, code: 6002, text: 'Committed batch to distribution database.'.
Publisher: {call sp_repldone ( NULL, 0x000ac8dc00003eca0029, 0, 250)}
Publisher: exec sp_replcounters N'APPORTAL'
Status: 4, code: 22021, text: 'Batch committed.  Batch consisted of 19 commands, 17 xacts.  Last xact: 0x000ac8dc00003eca0001, '0x000ac8dc00003eca0029'.'.
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Status: 4, code: 6002, text: 'Committed batch to distribution database.'.
Publisher: {call sp_repldone ( NULL, 0x000ac8dc00003eea0001, 0, 32)}
Publisher: exec sp_replcounters N'APPORTAL'
Status: 4, code: 22021, text: 'Batch committed.  Batch consisted of 1 commands, 1 xacts.  Last xact: 0x000ac8dc00003ee40001, '0x000ac8dc00003eea0001'.'.
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Status: 4, code: 6002, text: 'Committed batch to distribution database.'.
Publisher: {call sp_repldone ( NULL, 0x000ac8dc00003ef20001, 0, 16)}
Publisher: exec sp_replcounters N'APPORTAL'
Status: 4, code: 22021, text: 'Batch committed.  Batch consisted of 1 commands, 1 xacts.  Last xact: 0x000ac8dc00003eec0001, '0x000ac8dc00003ef20001'.'.
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Status: 4, code: 6002, text: 'Committed batch to distribution database.'.
Publisher: {call sp_repldone ( NULL, 0x000ac8dc00003f28001e, 0, 157)}
Publisher: exec sp_replcounters N'APPORTAL'
Status: 4, code: 22021, text: 'Batch committed.  Batch consisted of 4 commands, 4 xacts.  Last xact: 0x000ac8dc00003f280001, '0x000ac8dc00003f28001e'.'.
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Status: 4, code: 6002, text: 'Committed batch to distribution database.'.
Publisher: {call sp_repldone ( NULL, 0x000ac8dd000000460027, 0, 157)}
Publisher: exec sp_replcounters N'APPORTAL'
Status: 4, code: 22021, text: 'Batch committed.  Batch consisted of 11 commands, 11 xacts.  Last xact: 0x000ac8dd000000460001, '0x000ac8dd000000460027'.'.
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Status: 4, code: 6002, text: 'Committed batch to distribution database.'.
Publisher: {call sp_repldone ( NULL, 0x000ac8de000000940001, 0, 203)}
Publisher: exec sp_replcounters N'APPORTAL'
Status: 4, code: 22021, text: 'Batch committed.  Batch consisted of 25 commands, 25 xacts.  Last xact: 0x000ac8de0000008b0002, '0x000ac8de000000940001'.'.
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}
Status: 4, code: 6002, text: 'Committed batch to distribution database.'.
Publisher: {call sp_repldone ( NULL, 0x000ac8de000001ce0026, 0, 140)}
Publisher: exec sp_replcounters N'APPORTAL'
Status: 4, code: 22021, text: 'Batch committed.  Batch consisted of 18 commands, 18 xacts.  Last xact: 0x000ac8de000001ce0001, '0x000ac8de000001ce0026'.'.
Publisher: {call sp_replcmds (500, 0)}
Publisher: {call sp_replcmds (500, 0)}


********************************** STATISTICS *********************************
Execution time (ms): 346703
Work time (ms): 10203
Distribute Repl Cmds Time(ms): 7904
Fetch time(ms): 5302
Repldone time(ms): 217
Write time(ms): 5205
   Num Trans: 552  Num Trans/Sec: 54.101735
   Num Cmds: 560  Num Cmds/Sec: 54.885818
*******************************************************************************

Status: 1, code: 22037, text: 'The process was successfully stopped.'.
The process was successfully stopped.
Disconnecting from Publisher 'MySTGVIRPUB01'


If the process never processed the existing transactions when it's in the active/active mode I might understand or at least have something to look at maybe a network issue, But it process whatever transactions that are out there and when there's nothing left it quits.

Is there a difference between getting existing transaction that are published and waiting for the next ones?

zach_john
Starting Member

3 Posts

Posted - 2003-07-07 : 17:04:42

Okay no help from MS yet, let me run this across all you all.

If I open up cmd.exe and run "c:\mypath\logread.exe" -publisher [mypubsvr] -publisherDB [mypubdb] -Distributor [myDist] -DistributorSecurityMode 1 -Continuous

The process runs hot and normal.

When I take the same command and throw it into the SQL Agent replacing the Replication Transaction-Log Reader job step with the one command above, execute it as a CmdExec, the job step runs for a bit, then starts going through the all retry attempts and then it fails.

The repl agent never went through the retries, but like CmdExec it fails. From the command promt it never fails. Hmmm problem with the com object and SQL server Agent?

Any thoughts

John Zacharkan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources