Below script will help you find out how many undistributed commands are there. Also, do you see any errors in sql server or windows logs ? Is it just slow or any failures reported ?
SELECT ( CASE
WHEN mdh.runstatus = '1' THEN 'Start - ' + CAST(
mdh.runstatus AS VARCHAR)
WHEN mdh.runstatus = '2' THEN 'Succeed - ' + CAST(
mdh.runstatus AS VARCHAR)
WHEN mdh.runstatus = '3' THEN 'InProgress - ' + CAST(
mdh.runstatus AS VARCHAR)
WHEN mdh.runstatus = '4' THEN 'Idle - ' + CAST(mdh.runstatus AS
VARCHAR)
WHEN mdh.runstatus = '5' THEN 'Retry - ' + CAST(
mdh.runstatus AS VARCHAR)
WHEN mdh.runstatus = '6' THEN 'Fail - ' + CAST(mdh.runstatus AS
VARCHAR)
ELSE CAST(mdh.runstatus AS VARCHAR)
END ) [Run Status],
mda.subscriber_db [Subscriber DB],
mda.publication [PUB Name],
RIGHT(LEFT(mda.name, Len(mda.name) - ( Len(mda.id) + 1 )),
Len(LEFT(mda.name, Len(mda.name) - ( Len(mda.id) + 1 ))) - (
10 + Len(mda.publisher_db) + ( CASE
WHEN mda.publisher_db = 'ALL' THEN 1
ELSE Len(mda.publication) + 2
END ) )) [SUBSCRIBER],
CONVERT(VARCHAR(25), mdh.[time]) [LastSynchronized],
und.undelivcmdsindistdb [UndistCom],
mdh.comments [Comments],
'select * from distribution.dbo.msrepl_errors (nolock) where id = ' +
CAST(mdh.error_id AS VARCHAR(8)) [Query More Info],
mdh.xact_seqno [SEQ_NO],
( CASE
WHEN mda.subscription_type = '0' THEN 'Push'
WHEN mda.subscription_type = '1' THEN 'Pull'
WHEN mda.subscription_type = '2' THEN 'Anonymous'
ELSE CAST(mda.subscription_type AS VARCHAR)
END ) [SUB Type],
mda.publisher_db + ' - ' + CAST(mda.publisher_database_id AS VARCHAR)
[Publisher DB],
mda.name
[Pub - DB - Publication - SUB - AgentID]
FROM distribution.dbo.msdistribution_agents mda
LEFT JOIN distribution.dbo.msdistribution_history mdh
ON mdh.agent_id = mda.id
JOIN (SELECT s.agent_id,
maxagentvalue.[time],
SUM(CASE
WHEN xact_seqno > maxagentvalue.maxseq THEN 1
ELSE 0
END) AS undelivcmdsindistdb
FROM distribution.dbo.msrepl_commands t (nolock)
JOIN distribution.dbo.mssubscriptions AS s (nolock)
ON ( t.article_id = s.article_id
AND t.publisher_database_id = s.publisher_database_id
)
JOIN (SELECT hist.agent_id,
MAX(hist.[time]) AS [time],
h.maxseq
FROM distribution.dbo.msdistribution_history hist (
nolock)
JOIN (SELECT agent_id,
Isnull(MAX(xact_seqno), 0x0) AS
maxseq
FROM
distribution.dbo.msdistribution_history (
nolock)
GROUP BY agent_id) AS h
ON ( hist.agent_id = h.agent_id
AND h.maxseq = hist.xact_seqno )
GROUP BY hist.agent_id,
h.maxseq) AS maxagentvalue
ON maxagentvalue.agent_id = s.agent_id
GROUP BY s.agent_id,
maxagentvalue.[time]) und
ON mda.id = und.agent_id
AND und.[time] = mdh.[time]
When you create a publication, you can choose whether or not you want to create a snapshot. Similarly (and more importantly), when you create a subscription, you can say if you want it to initialize or not. Choosing not to is probably what you need, but I suggest you test it before doing it in production
Best Answer
The stored procedure sp_addsubscription allows for you to specify that you want all articles (i.e. tables) or just one. If you want a subset of articles in a publication, you just need to run the sp_addsubscription once for each article that you want. It looks like the sp_addsubscription defaults to @article = 'all' My suggestion is to go through the New Subscription Wizard and have it generate the script only. Then you can go in and modify the script as you need it. You would need to add multiple sp_addsubscription lines.