34,590
社区成员
发帖
与我相关
我的任务
分享
-- 查询数据库有那些同步
SELECT sct .dest_db ,srt . dest_owner , srt. name , sct . srvname , pub.pubid , srt . dest_table --,srt.artid ,sct.artid
FROM syspublications as pub WITH (NOLOCK)
inner join sysarticles as srt WITH( NOLOCK) on pub . pubid = srt .pubid
inner join syssubscriptions sct with (nolock) on srt.artid = sct.artid
GROUP BY sct. srvname , pub . pubid , srt. dest_owner , srt. name , srt.dest_table ,sct .dest_db
--查看实例中所有复制数据库
SELECT name ,is_published FROM sys .databases WITH(NOLOCK )
WHERE is_published = 1 AND state = 0
-- 查看对应数据库有多少未分发命令
use distribution
go
select count (*) from msrepl_commands with(nolock )
where publisher_database_id in (
select database_id , name from sys. databases
where name = N'XXX')
-- 查询各库所有的分发命令
use distribution
go
select publisher_database_id ,article_id ,cnt = COUNT(*)
from msrepl_commands with( nolock)
group by publisher_database_id ,article_id
-- 查看数据库发布的发布名称
use DB_NAME
go
exec sp_helppublication
-- 数据库有那些发布项目
use DB_NAME
go
exec sp_helparticle
@publication = 'xxx'
-- 根据article_id 也确认是那个表有问题
use distribution
go
select article_id , cnt = COUNT(*) from msrepl_commands with (nolock)
where article_id in ( 32 , 3)
group by article_id
-- 查看表是否发布重复 ,通常这个再有事务的时候就会出现主键冲突。
use YinTaiOrder
go
select object_name (art .[objid] ) objectame , dest_table , art. pubid , is_replicated , is_published -- ,*
from sysarticles art with(nolock ) INNER JOIN sys.tables tab with(nolock ) on art .objid = tab .object_id
group by dest_table ,objid ,fire_triggers_on_snapshot ,art. pubid , is_replicated , is_published
having COUNT (dest_table )> 1
-- 查询快照对应的JOB
use distribution
go
select jobs .name, publisher_db, publication, s.data_source as publisher,
case publication_type when 0 then 'Transactional'
when 1 then 'snapshot'
when 2 then 'Merge'
end as publication_type
From MSsnapshot_agents a inner join sys. servers s on a.publisher_id =s. server_id
inner join msdb.. sysjobs jobs on a.job_id =jobs. job_id
-- 查询日志读取器对应的JOB : 同一个数据库的多个 publication共用一个Logreaderagent )
select jobs .name, publisher_db, s.data_source as publisher
From MSlogreader_agents a inner join sys. servers s on a.publisher_id =s. server_id
inner join msdb.. sysjobs jobs on a.job_id =jobs. job_id
-- 查询分发代理JOB
select isnull (jobs. name,a .name) as name, a.publisher_db ,a. publication as publicationName,s .name as publisherName ,s .data_source as publisherName,s1 .data_source as subscriber ,a. subscriber_db, a.local_job
From MSdistribution_agents a
inner join sys. servers s on a.publisher_id =s. server_id
inner join sys. servers s1 on a.subscriber_id =s1 .server_id
left join msdb.. sysjobs jobs on a.job_id =jobs. job_id
where a .subscription_type <>2