SQL Server replication Common Errors

1. Error:The subscription has been marked as inactive and must be reinitialized. You need to delete nosync subscriptions and recreate them.

Use Distribution
Go
-- Abnormal release in search status
Select Status, * From DBO. mssubscriptions Where Status <> 2
-- Modify status
Update DBO. mssubscriptions Set Status = 2 Where Status <> 2

Check whether the replication chain can run normally. Then, use tablediff to compare whether the table data in the publishing chain is consistent.
Snapshots can also be reinitialized, but a large amount of data is a tragedy.

2. Error:Tables, stored procedures do not exist, and table structures are inconsistent.
Solution:
A. the table or stored procedure does not exist:
Fill in the library corresponding to the subscription end with the missing objects;

A strange phenomenon is that the subscription table exists clearly, but the synchronization chain still reports an error. There may be two situations:
1. Table fields are inconsistent. You can fill in the table fields by referring to (B) below;
Second, the table fields are the same, but the error is still reported. You can solve the problem in three ways:
A. Pause the synchronization link and enable it again to see if it can pass. If not, perform the following steps;
B. Check the table with an error reported in the synchronization link, and observe the synchronization link. The release of this table is removed. errors can generally pass,
After all the data is synchronized, you can select this table. After the synchronization chain is normal, use the tablediff tool to compare
Data in this table;
C. If step B still reports an error, it can only be rebuilt.

B.The column name 'xxx' is invalid.
You can use the following statement to find the table corresponding to the missing Field

-- Search for a field in a release chain (rp_xxxx release name xxx field name)

Select A. Name As Table_name, B. Name As Column_name ,( Select ' ALTER TABLE ' + A. Name + ' Add ' + B. Name + ' ' +
( Case When Name = ' Nvarchar ' Then Cast (B. max_length / 2 As Varchar )
When Name Like ' Date % ' Or Name = ' Money ' Then ''
Else Name End )
From SYS. Types Where System_type_id = B. system_type_id) As Definition From DBO. sysarticles With (Nolock) Inner Join SYS. columns B With (Nolock)
On A. objid = B. Object_id Inner Join DBO. syspublications C With (Nolock) On A. pubid = C. pubid
Where C. Name = ' Rp_xxxx ' And B. Name = ' Xxx '

3. Error :.Primary key conflict
Solution: Skip the error

After the error is completed, you need to compare the data on both sides (compared with the data volume)

-- ------------- Skip the error on the subscription machine ------------------------
-- --------------------- On the distribution machine --------------------------
-- Syntax
Sp_helpsubscriptionerrors [ @ Publisher = ] ' Publisher '
, [ @ Publisher_db = ] ' Publisher_db '
, [ @ Publication = ] ' Publication '
, [ @ Subscriber = ] ' Subscriber '
, [ @ Subscriber_db = ] ' Subscriber_db '

--Get publisher subscriber
Select*FromMssubscriber_info

--Get publisher_db publication subscriber_db = publisher_db
Select*FromMspublications

-- Example
Sp_helpsubscriptionerrors ' Sqlw2k8 ' , ' Dbtranpub ' , ' Dbtranpub_pub ' , ' Sqlw2k8subscriber ' , ' Dbtransub '

-- Obtain the xact_seqno Value

-- -------------------- On the subscription machine ---------------------------
Sp_setsubscriptionxactseqno [ @ Publisher = ] ' Publisher ' ,
[ @ Publisher_db = ] ' Publisher_db ' ,
[ @ Publication = ] ' Publication ' ,
[ @ Xact_seqno = ] Xact_seqno

--Example
Sp_helpsubscriptionerrors'Sqlw2k8','Dbtranpub','Dbtranpub_pub', Xact_seqno

--Bytes -----------------------------------------------------------------------------------

4. Error:The row cannot be found on the subscription server when the command copied by the application is run.

A. You can use the "primary key conflict" error processing method to skip the error and use tablediff to compare the data difference between the two tables;
B. Add missing data to the subscription end.

-- Use the following statement to find the error number
Select Top 100 E. xact_seqno, E. command_id, E. *
From DBO. msdistribution_history H
Join DBO. msrepl_errors E On H. error_id = E. ID
Where Comments Not Like ' % Transaction % ' -- Failed proxy
Order By ID Desc

-- Use the specific transaction serial number found above to view the specific commands executed by the Replication Component
-- Run sp_browsereplcmds on the distribution database. Note that the start and end of xact_seqno must be specified.
Sp_browsereplcmds ' 0x0000003b00000020000500000000 ' , ' 0x0000003b00000020000500000000 '
-- The result is as follows:
Article_id command
1 {Call [ DBO ] . [ Sp_msdel_dboupccodetransaction ] ( ' 000000002 ' )}

-- Find the corresponding object
Select Publisher_db, article From DBO. msarticles Where Article_id = 1 And Publication_id = (
Select Publication_id From Mspublications With (Nolock) Where Publication = ' Rp_xxx ' )

--It can be seen from sp_msdel that this command is a delete statement, because the data on the publishing end does not exist, so it can only be skipped;

--If it is a modification, You need to verify whether the above data exists on the faulty subscription server. If it does not exist, add it.

5. Error:User 'xxx' logon failed or the process cocould not connect to subscriber 'xxxx'.
Solution: Check whether the account is correct. You can log on to the publishing and subscription servers and have the relevant permissions.

6.The initial snapshot of 'xxx' is not available yet.
Ensure that the SQLServerAgent is running. Copy = published content = published project =, right-click subscription on the right, and reinitialize the agent.
If not, copy the monitor -- Publishing Server -- XXX -- published Project -- snapshot and check that the process failed to create the file "\ xxz \ sqlpub \ UNC ",
To this folder, reconfigure the SQL account to grant the "full" permission to this folder ".

7.The process failed to replicate large capacity from the table "[DBO]. [syncobj_0x3745373834413345 ]"
An I/O error occurs when writing BCP data files (Source: odbc SQL Server Driver (ODBC); ErrorCode: 0)
Solution: change all character types to n (char-nchar, varchar-nvarchar, ntext ).

8. error message:
"The agent 'xxxx' is trying again after an error occurs. You have tried 25 times. For more information, see Agent job history in the jobs folder."

This error is generally caused by a large number of release chains on a machine. The error is caused by database restrictions on the memory usage of replication,
We need to change this restriction to solve this problem (it is best not to create too many release chains on another machine). The method is as follows:

HKEY_LOCAL_MACHINE \ SYSTEM \ CurrentControlSet \ Control \ Session Manager \ subsystems \
Open windows and find the following content
% SystemRoot % \ system32 \ csrss.exe objectdirectory = \ Windows sharedsection = 768
Change the last number (not necessarily this number, maybe it is, etc.) to 1024 and save it. restart the system.

If Windows server is not amdin, the user may not be allowed to open the Registry. If it is a management group permission, you can go
In c: \ windows \ system32, right-click the regedit32.exe file and add your account to the running permission.

9. -- cannot drop the database XXX because it is being used for replication
exec sp_removedbreplication 'database'