Replication General Errors


Msg 2560, Level 16, State 9, Procedure sp_MSremoveidrangesupport, Line 88
Parameter 1 is incorrect for this DBCC statement.
Msg 20025, Level 16, State 1, Procedure sp_dropmergepullsubscription, Line 339
The publication name must be unique. The specified publication name ‘MainSync’ has already been used.
Msg 14056, Level 16, State 1, Procedure sp_dropmergepullsubscription, Line 426
The subscription could not be dropped at this time

Solution :

You can use sp_removedbreplication, but have to make again all publication and subscriptions.

Replication Questions and Answers

A) How to check whether the subscriber is in sync with the Publisher without using GUI(EM)?

Step1:  On the distributor, Check the log reader agent is running fine or not using the EM. If this runs fine proceed with the next step else troubleshoot it to fix the issue.

Step 2: On the subscriber, connect to the subscriber database and execute the following query:

        Select * from <subscriber database>.dbo.Msreplication_subscriptions.

This will give all details related to the subscriptions. In these entries, take the value (omit all exceeding zeros) for transaction timestamp field, which is nothing but the last xactseqno got loaded in the subscription database. There will be only one row present for this table in subscription database unless if this database is subscribed to many publications. Hence transaction timestamp correlates to individual publications. Once you got the transaction we can easily know how many transactions yet to be delivered to the subscriber database from the distribution database.

In GUI interface we can make use of Replication monitor to verify the sync status.

For example: 0x000004570001E3FE000100000000 is the current value for transaction timestamp.

Xactseqno is 0x000004570001E3FE0001 (Exclude the exceeding zeros).

Step 3: Using the above xactseqno, check out with the following commands and identify what are all the transactions that are yet to be delivered to the subscribers. If there are no rows returned from the below query, indicates the replication is in sync. In addition to this, check log reader agent is running fine or not.

       Select * from Msrepl_commands where xact_seqno > 0x000004570001E3FE0001

B) How to avoid rogue red color in Replication Monitor?

Rogue red Color: If any agent fails in the distributor, it displays an icon with a red circle in Replication monitor.

There are may be a case that some of the failed replication entries may be invalid (present in the tempdb database) and because of these rogue red color stays in the replication monitor.

The above replication monitor entries are get maintained in the tempdb database in Msreplication_agent_status. Refreshing the table will resolve this issue.

Four ways to remove rogue red color:

1) Open the Msreplication_agent_status and check which agent has the status as 6.Make note of the agent that has the status as 6 and go back to Replication Monitor > Agents and check the status of that agent, if it is in a failed state then we have to fix that error. If the agent is successfully running, then you can change the Status value from 6 to 4 and then refresh the Enterprise Manager.

2) Open the table, check which agent has status as 6 and if that agent doesn’t exists on distribution agent. Remove the entry from that table and refresh the enterprise manager.

3) Use procedure sp_MSload_replication_status which will refreshes the tempdb table and rogue red color will be removed after refreshing enterprise manager.

4) Recycle the SQL server and it will recreates the tempdb database in which in turn refreshes all the tables.

C) In SQL server 2005, below error will occur whenever if you try reinitializing an agent.

Cause:

Subscription needs to be reinitialized if any snapshot needs to be applied for any broken replication. In this case Subscriptions cannot be reinitialized for non-immediate_sync publications.

WorkAround:

Connect to the subscriber database, in the Msreplication_subscriptions table; the value of immediate_sync should be changed to 1. 0 indicates non-immediate sync publications

Now it will allow us to reinitialize the subscribers and we can generate snap shot from the publishers successfully.

D) “Cannot insert duplicate key row in object ‘impNodes’ with unique index ‘PK_impNodesToDelete’. The statement has been terminated.”

Cause:

This problem occurs if when a row which is to be delivered on the subscriber already exists on the subscriber database.

This occurs as a part of insertion operations, so sp_MSins_impNodes procedure with value of primary key will be executed on the subscriber, if it finds that value already exists in the subscriber table, it reports the above error and fails. These procedures are custom written based on the requirements.

If the below workaround doesn’t helps and if you get the same error for the other table, then proceeding with reinitializing the subscription or investigate what causes this error in the first place.

 WorkAround:

Follow the below steps to fix the above error:

1)       In the error history get the last transaction seqno(xact_seqno) and in the similar way compare the MSReplication_Subscriptions transaction timestamp. Surely this will match and try to find the next xactseqno (first row from the below result).

          Select * from Msrepl_commands where xact_seqno > (value from transaction timestamp)

           — This table is present in the distribution database.

2)      Use the following command and verify that the next xactseqno points to that the transactions which has replicated to the subscriber is same.

           sp_browsereplcmds @xact_seqno_start=’XXXXXXXXXXXX’,

          @xact_seqno_end=’XXXXXX’,

           @publisher_database_id=4,

           @command_id=1[optional]

        3) Now check the transaction is consistent on both publisher and subscribers.

        4) If so, then update that xactseqno by adding 8 zeros to it.

             Update Msreplication_subscriptions set transaction_timestamp = ‘0x…….’ where 
             Publication =   ‘……’

5)       Then start the distribution agent, it will start applying from the next xactseqno after the updated one.

E)   “Timeout Expired.”           

Cause:

If the log reader agent or distribution agent is trying to connect to the publisher or distributor (subscriber) and querying for more than 300 seconds (default), will result in this error.

WorkAround:

Step 1: Set the retry interval of the job to 2 or 3, then try rerunning the job. If this doesn’t help, then move to next step.

Step 2: Increase the QueryTimeout to 1000. This can be implemented either by specifying the parameter in job under Run agent step or create a new agent profile/Default and give value as 1000 for QueryTimeout.

F) “The row was not found at the Subscriber when applying the replicated command.”

Cause:

This problem occurs when a row which is to be deleted on subscriber doesn’t exists.

This occurs as a part of deletion operations, so sp_MSdel_<article> procedure with value of primary key needs to be deleted on the subscriber. If that value doesn’t exists in the table, it reports the above error.

If you get the same error for the other table even after performing the below workaround, then proceeding with reinitializing the subscription or investigate what causes this error in the first place.

WorkAround:

1) Get the latest xact_seqno from the MSreplication_Subscriptions

2) Compare it with the distribution..Msrepl_commands table and retrieve the next record using the following query:

   Select * from Msrepl_commands where xactseqno > 0x000004570001E3FE0001

3) Use below query to find on which transaction does the agent fails:

sp_browsereplcmds @xact_seqno_start=’XXXXXXXXXXXX’,

@xact_seqno_end=’XXXXXX’,

@publisher_database_id=4,

@command_id=1

If the data retrieved from this command is consistent with the data on the subscriber, then update the column transaction_timestamp in the Msreplication_subscriptions with the xact_seqno of the failed transaction and start the distribution agent.

4) Or else add –skiperrors <error number> parameter in the agent profiles to avoid this alert.

G) “The agent is suspect. No response for the past 10 minutes.”

Cause:

If an agent is running and has not logged any activity in 10 minutes, it will report this as warning and is displayed as an error in the

WorkAround:

1) By default, the Log Reader Agent polls the published database’s transaction log every 10 seconds. Increase the polling interval will rectify the above error.

2) Increase the Inactivity Threshold value to suppress this error message. Just
right click the Replication Monitor node, click Refresh Rate and Settings, and then
increase the Inactivity Threshold values. By default Inactivity Threshold value is 10 minutes.

H) “The replication agent is not registered properly. Rerun SQL Server setup. The step failed.”

Cause:

The Replication Agent that is running on the server may not restart when a named instance of SQL Server is removed from the server and replication is configured on the server. When you remove a named instance of SQL Server, the registry entries that relate to the Replication Agent are removed from the server. This behavior may also occur when the 8.3 name creation on the NTFS file system partition is disabled on the computer where SQL Server is installed. You disable 8.3 name creation on the NTFS partition by setting the NtfsDisable8dot3NameCreation registry key to 1.

WorkAround:

1. Set the NtfsDisable8dot3NameCreation registry key(HKEY_LOCAL_MACHINE\SYSTEM\CURRENTCONTROLSET\CONTROL\FileSystem)  to0.

2. Restart your computer.      

In addition to this, we do have other ways to rectify it. The below link will give more details on this issue.

http://support.microsoft.com/default.aspx?scid=kb;en-us;811008

I) “Could not execute sp_MSadd_repl_commands.”

Cause:

If any other user is executing the repl commands in the publisher and his session is quite open for a long time will produce this error.

Workaround:

If this issue occurs 2 or 3 times, we can ignore it and simply start the logreader jobs, it will succeed.

Notes:

For troubleshooting any issues in Replication, we need to add output log file to track at what stage failure happens in the agent. Add the below parameter to any agents which in turn creates log file on the specified drive.

-Output C:\repllog.txt –OutputVerboselevel 2

Important procedure used to troubleshoot  Replication:

sp_showreplcmds :

Returns the commands for transactions marked for replication in readable format.

 This stored procedure is executed at the Publisher on the publication database.

sp_replshowcmds:

Run only when client connections (including the current connection) are not reading replicated transactions from the log. This stored procedure is executed at the Publisher on the publication database.

Example: sp_replshowcmds <maxtrans>

maxtrans — > specifies the maximum number of transactions pending replication for which

sp_replshowcmds returns information.

Note:

Clients that run sp_replshowcmds and sp_replcmds within the same database receive error 18752(as discussed under above section H)

To avoid this error, the first client must disconnect or the role of the client as logreader must be released by executing sp_replflush. After all clients have disconnected from the logreader, sp_replshowcmds can be run successfully.

sp_repltrans:

Returns a result set of all the transactions in the publication database transaction log  that are marked for replication but have not been marked as distributed. This stored procedure is executed at the Publisher on a publication database.

sp_repltrans is similar to sp_replcmds (Transact-SQL) but does not return the commands for the transactions. sp_repltrans is used in transactional replication and is not supported for non-Microsoft SQL Server Publishers.

sp_replcmds:

Returns the commands for transactions marked for replication. This stored procedure is executed at the Publisher on the publication database.

sp_browsereplcmds:

Returns a result set in a readable version of the replicated commands stored in the distribution database, and is used as a diagnostic tool. This stored procedure is executed at the Distributor on the distribution database.

sp_browsereplcmds @xact_seqno_start=’0x0004453600000F8C000B’,
@xact_seqno_end=’0x0004453600000F8C000B’,
@publisher_database_id=4,
@command_id=1 [Optional]

To find location where the snap shot generated:

a. Below procedure returns the complete path for the latest snapshot generated for a publication. This stored procedure is executed at the Publisher on the publication database.

Eg: sp_browsesnapshotfolder <publishername>

b. This table should be executed in the distributor server.

select * from msdb.dbo.MSdistpublishers

How to find the T-log records:

select top 10 * from ::fn_dblog( default, default ) order by “Current LSN” desc
DBCC LOG(5,2) — (dbid,detailedlist) Applicable only for small databases

dbcc log(dbname)

Important Commands used in distribution database

select * from msdb..Msdistributiondbs
select * from msdb..msdistributor
select * from Mspublications
select * from Mspublisher_databases
select * from msdb..MSreplication_objects
select * from MSreplication_subscriptions
select * from MSrepl_errors
select * from MSrepl_originators
select * from MSrepl_transactions
select * from MSrepl_version
select * from MSsubscriber_schedule
select * from Mssubscriptions
select * from MSsubscriptions_properties

All agents are implemented in Replication Monitor graphically , in order to view the details in the tables, below query will give more details:

Select * from Mslogreader_agents
select * from Msdistribution_agents
Select * from Msqueuerader_agents
Select * from MsMerge_agents
Select * from MsSanpshot_agents

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: