复制订阅知识l进阶

一：  生成快照的俩种方式： 

PS:  修改MSpublications 中的俩个参数。

@allow_anonymous = N'false' ,@immediate_sync = N'false' ：当发布中添加新项目后，创建初始化快照时，快照将以增量方式创建
@allow_anonymous = N'true' ,@immediate_sync = N'true' ：当发布中添加新项目后，创建初始化快照时，该发布下面所有项目的快照将全部重新初始化

如果是以增量方式创建的发布，在用脚本向发布中添加项目时，该发布相关的订阅需重新创建，即再次执行sp_addsubscription过程，否则在做快照初始化时会提示“由于没有需要初始化的订阅，因此未生成快照”。如果是以图形界面来勾选项目的话，就没这个问题，可以直接创建快照，估计这个sp_addsubscription步骤是被自动执行。

发布创建后，可以通过 sp_changepublication来变更参数

sp_changepublication @publication = 'test'
, @property = 'immediate_sync'
, @value = false
GO
sp_changepublication @publication = 'test'
, @property = 'allow_anonymous'
, @value = false
GO

 PS:  其实修改的是此表中的俩个字段信息  ---- SELECT  * FROM dbo.MSpublications s

最后，如果@allow_anonymous = N'true' ,分发库中的MSrepl_commands和MSrepl_transactions表的事务记录会保留@max_distretention 设定的时长，因为我的发布项目数量很多，所以导致这两个表的记录数相当宠大。

二： 复制订阅错误处理测试

返回给定订阅的所有事务复制错误。 此存储过程在分发服务器的分发数据库中执行：

sp_helpsubscriptionerrors [ @publisher = ] 'publisher'
, [ @publisher_db = ] 'publisher_db' 
, [ @publication = ] 'publication' 
, [ @subscriber = ] 'subscriber' 
, [ @subscriber_db = ] 'subscriber_db'

--get publisher subscriber
select * from MSsubscriber_info

--get publisher_db publication subscriber_db=publisher_db
select * from MSpublications

用于指定订阅服务器上的分发代理应用的下一个事务的日志序列号 (LSN)，从而使代理可以跳过失败的事务。 此存储过程在订阅服务器上对订阅数据库执行

sp_setsubscriptionxactseqno [ @publisher= ] 'publisher', 
[ @publisher_db= ] 'publisher_db', 
[ @publication= ] 'publication', 
[ @xact_seqno= ] xact_seqno

-使用如下语句找出错误号
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%' --失败的代理
order by id desc

--用上面查到的具体事务序列号xact_seqno,查看复制的具体命令信息
--在分发数据库上执行:sp_browsereplcmds,注意必须限定开始和结束xact_seqno
sp_browsereplcmds '0x0000003B00000020000500000000','0x0000003B00000020000500000000'

三  错误排查流程

 原理： 

1)  日志读取器（LogReader）的读线程通过存储过程sp_replcmds（xp_replcmds的包装）读取事务日志，它会扫描被标记为复制事务的日志，跳过非复制的事务。

2)  日志读取器的写线程使用sp_MSadd_replcmds，将排队事务从读者线程写到分发数据库。

3)  分发的读线程执行sp_MSget_repl_commands，从分发数据库获得未处理的命令并存储到内部队列。

4)  分发的写线程通过以sp_MSupd、sp_MSins、sp_MSdel开头的几个参数化的存储过程，将行的变更应用到订阅方的每个项目，从而将队列中的命令写到订阅服务器。

5)  日志读取器和分发服务器也会执行一个“历史”线程，将总结数据写到分发数据库的系统表MSlogreader_history和MSdistribution_history中

 

  2）如何输出出错的信息
   在待输出出错信息的JOB命令最后加入 -Output c:\temp\error.txt, -Output是命令,后面是待输出的文件路径和文件夹名.

Fetch time(ms): 3343920   <--读时间为55分钟

Write time(ms): 140070    <--写时间为3分钟

 Time to Apply Cmds (ms) : 14110  Cmds/Sec       : 3.69   <-- 写线程

Time to Retrieve Cmds (ms): 92  Cmds/Sec : 565.22      <-- 读线程

1  日志代理器读：

sp_replcmds  @maxtrans = 1    --返回下一个待完成事务的所有命令

sp_replshowcmds @maxtrans = 1        --返回从下一个待完成事务开始的所有命令及命令文本

SELECT dbid, begin_lsn, commit_lsn,rows, cmds_in_tran FROM sys.dm_repl_traninfo  

---显示最后一次被sp_replcmds或者sp_replshowcmds读取的日志中复制事务的总结信息，包括“cmds_in_tran”。值比较大可能说明有一个大型事务正在被复制。

DBCC SQLPERF (LOGSPACE)     --- Database Name ， Log Size (MB)， Log Space Used (%)， Status
DBCC LOGINFO                        -----大量的虚拟日志文件 pub_DB

显示最后一次被sp_replcmds或者sp_replshowcmds读取的日志中复制事务的总结信息，包括“cmds_in_tran”。值比较大可能说明有一个大型事务正在被复制。

2  日志读取器写线程   sp_MSadd_replcmds的dm_exec_query_stats

--以top total_worker_time排序

SELECT TOP 25

  st.text, qp.query_plan,

    (qs.total_logical_reads/qs.execution_count) as avg_logical_reads,

    (qs.total_logical_writes/qs.execution_count) as avg_logical_writes,

    (qs.total_physical_reads/qs.execution_count) as avg_phys_reads,

  qs.*

FROM sys.dm_exec_query_stats as qs

         CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st

         CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) as qp

WHERE st.text like 'CREATE PROCEDURE sp_MSadd_replcmds%'

  ORDER BY qs.total_worker_time DESC

------------------------------------- like 'CREATE PROCEDURE sys.sp_MSget_repl_commands%'

 分发代理器读：    -- 查看复制表的表统计信息，尤其是'Updated'（被更新）的日期

DBCC SHOW_STATISTICS(MSrepl_commands,ucMSrepl_commands)

DBCC SHOW_STATISTICS(MSrepl_transactions,ucMSrepl_transactions)

UPDATE STATISTICS MSrepl_transactions WITH FULLSCAN 

UPDATE STATISTICS MSrepl_commands   WITH FULLSCAN

 

分发代理器写：

1）是否有触发器

2） 查看所有的项目是否有SQL语句被作为批的 “参数”来发送。状态status=8说明“参数”批没有被启用。

    exec sp_addarticle @publication = ... @status = 24

或

   select name, status from sysarticles

 

四 、 含有Text域的数据发布问题

    BCP数据文件中遇到意外的EOF(源:ODBC SQL Server Driver(ODBC):错误代码:S1000)
    这是因为数据库中含有TEXT或nTExt或Image字段引起的,只要在某项发布属性页中选择<快照>-<快照格式>-<字符模式格式>,即可解决该问题.

   2）、对某些大型数据表无法生成快照的问题，数据库报如下错误：
        进程未能从表"[dbo].[syncobj_0x3436383044343035]"向外大容量复制，在写 BCP 数据文件时发生 I/O 错误(源: ODBC SQL Server Driver (ODBC); 错误代码: 0)
     这是因为该表有问题或表数据太大造成的，可用如下方法试着解决一下：
     a、用DBCC checktable 对该表检查一下，数据表是否有问题，然后再试一下重新生成数据快照。
     b、如果上述问题仍无法解决，可以用数据导出功能将数据表导到订阅服务器中。
     c、然后重建一个针对该数据表的数据发布，选择不生成快照功能，在发布服务器上运行如下命令：sp_scriptpublicationcustomprocs 'xxxx', 其中xxxx为发布名称，该命令会生成创建上述三个存贮过程的脚本，然后在订阅服务器运行生成三个存贮过程即可。，解决出现的问题。