July 24, 2007 at 7:34 am
I am running SQL Server 2000 SP2 and I have a log reader agent that can't read the transaction log anymore. I have researched the problem at various websites. The log reader error message is: 'The process could not execute 'sp_replcmds' on 'PRODSQLS05'.'
I added "-Output C:\Temp\verbose_log_reader.txt -Outputverboselevel 2" to the options for LogRead.exe and here is a sample of the output:
---------------- output follows below --------------------
Microsoft SQL Server Log Reader Agent 8.00.534
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: PRODSQLS05-NMPHD-1
Connecting to Publisher 'PRODSQLS05.NMPHD'
Server:
DBMS: Microsoft SQL Server
Version: 08.00.0534
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/23/2007 12:54:20 PM]PRODSQLS05.NMPHD: select @@SERVERNAME
[7/23/2007 12:54:20 PM]PHDWICSQLS05.NMPHD: 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 ( 0x00010450000287ba005e, 0x00010450000287ba005e, 0, 0)}
Publisher: {call sp_replcmds (500, 0)}
Status: 4, code: 20051, text: 'Delivering replicated transactions'.
.
.
.
.
Status: 4, code: 20051, text: 'Delivering replicated transactions'.
ed transactions'.
Status: 2, code: 0, text: 'The process could not execute 'sp_replcmds' on 'PRODSQLS05'.'.
The process could not execute 'sp_replcmds' on 'PRODSQLS05'.
Status: 2, code: 0, text: 'Timeout expired'.
Disconnecting from Publisher 'PRODSQLS05'
The agent failed with a 'Retry' status. Try to run the agent at a later time.
Microsoft SQL Server Log Reader Agent 8.00.534
Copyright (c) 2000 Microsoft Corporation
Microsoft SQL Server Replication Agent: PRODSQLS05-NMPHD-1
------------------------ output sample above ------------------------
I have verified that sp_replcmds functions, and it does.
The thing that confuses me the most is the result of 'DBCC opentran':
---------- DBCC opentran output below --------------
The results of dbcc opentran:
Transaction information for database 'NMPHDWIC'.
Replicated Transaction Information:
Oldest distributed LSN : (66640:165818:94)
Oldest non-distributed LSN : (66640:158514:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
------------------- DBCC opentran above ---------------------------
These LSN's translate to 0x00010450000287BA005E for the oldest distributed
and 0x0000104500026B320001 for the oldest non-distributed LSN. It seems reasonable that the oldest nondistributed LSN should have a higher number than the oldest distributed. How can this be?
I suspect there may be a large delete transaction stuck in my log that may be timing sp_replcmds out, but I am confused by the output of DBCC opentran. What do you suggest?
Thanks
July 24, 2007 at 8:57 am
I solved the problem!
I was and still am confused why the oldest non-distributed LSN is a smaller number than the oldest distributed LSN. I thought my transaction log must've been corrupted.
This is how I solved the problem:
Using chapter 9 of "A Guide to SQL Server 2000 Transactional & Snapshot Replication" by Hillary Cotter as a guide, I executed 'sp_replcmds 500,0,-1' in Query Analyzer and it took so long to execute that I stopped the execution. To my surprise there was data there, an enormous amount of data. When I ran sp_replcmds I was asking for 500 transactions, I instead ran sp_replcmds 2,0,-1 and I got my data back. There is a parameter to LogReader.exe call -ReadBatchSize which I set in the Log Reader Agent properties (by hitting the edit button on step 2 in the Log Reader Agent steps tab of the properties panel) to 2. I started the agent and data began to move slowly.
I'm leaving work early today.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply