February 26, 2009 at 3:12 am
Hi,
Any advice would be appreciated as I'm a newbie to replication. Unfortunately I've come across what seems to be a really nasty issue when a column is added to an article that is then causing the Log Reader to fail.
I'm running
- SQL Server 2005 Enterprise Edition x64 (SP3)
- Windows Server 2003 x64 Standard (SP2)
Through trial and error I have found this only seems to occur on tables we have partitioned. After a column is added it seems to corrupt any publications on the database.
LogReader Agent Status:
The process could not execute 'sp_replcmds' on 'sql02'.
Running sp_replcmds manually fails with:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Agent History Log:
The process could not execute 'sp_replcmds' on 'servername'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
Unspecified error (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
The process could not execute 'sp_replcmds' on 'servername'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037%5B/i%5D
I've tried sp_repltrans and sp_replflush and dbcc open tran and these have not thrown up any clues. I've also tried sp_repldone and backing up the log. I've modifed the Log Reader profile LoginTimeout to 50, QueryTimeout to 50000 and ReadBacthSize to 1. Even dropping the publication and recreating it does not resolve the error.
I can't be the only one with this issue. Please help!
February 26, 2009 at 9:17 am
cheeky1981 (2/26/2009)
Hi,Any advice would be appreciated as I'm a newbie to replication. Unfortunately I've come across what seems to be a really nasty issue when a column is added to an article that is then causing the Log Reader to fail.
I'm running
- SQL Server 2005 Enterprise Edition x64 (SP3)
- Windows Server 2003 x64 Standard (SP2)
Through trial and error I have found this only seems to occur on tables we have partitioned. After a column is added it seems to corrupt any publications on the database.
LogReader Agent Status:
The process could not execute 'sp_replcmds' on 'sql02'.
Running sp_replcmds manually fails with:
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Agent History Log:
The process could not execute 'sp_replcmds' on 'servername'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
Unspecified error (Source: MSSQLServer, Error number: 0)
Get help: http://help/0
The process could not execute 'sp_replcmds' on 'servername'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037%5B/i%5D
I've tried sp_repltrans and sp_replflush and dbcc open tran and these have not thrown up any clues. I've also tried sp_repldone and backing up the log. I've modifed the Log Reader profile LoginTimeout to 50, QueryTimeout to 50000 and ReadBacthSize to 1. Even dropping the publication and recreating it does not resolve the error.
I can't be the only one with this issue. Please help!
- The First thing is that you should NEVER run sp_replflush or sp_repldone unless you really know what you are doing!
-Second:
When the error is so esoteric it usually represents an abnormal condition. You should probably patch your server.
Here is a HotFix that may or may not be your issue: http://support.microsoft.com/kb/910416
* Noel
August 20, 2009 at 1:28 am
I am HAving SQL server 2005
Even i would like to have the Hot fix downloaded, i am not getting the link from where to download.
In My scenario, i had a Server A as Principal with DB1 and Server B as Mirror With DB1 acting as Mirror and DB1_Sub in server B for Replication to happen. I am not having the witness.
I configured Mirroring from Server A to Server B.I had transactional Replication from DB1 of Server A which is also Principal with the DB1_Sub which acts a subscriber db in Server B.
Once a Failover is made, failover happens but the Changes done to DB1 in Server B which acts as Principal once the failover happens is not replicating to DB1_Sub DB which is in Server B.
I have also exectue the command
exec sp_add_agent_parameter
@profile_id = 6, @parameter_name = N'-PublisherFailoverPartner',
@parameter_value = N'DB NAme'
exec sp_add_agent_parameter
@profile_id = 1, @parameter_name = N'-PublisherFailoverPartner',
@parameter_value = N'DB Name'
how do i fix this issue pls let me know.
With Regards
Dakshina Murthy
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply