Using replication procedures to troubleshoot SQL Server problems
This article describes how you can resolve SQL Server problems by using the SQL Server replication
stored procedures.
You can use the sp_addarticle stored procedure to resolve problems with
replication changes to subscribers.
You can encounter that the updates to columns may not replicate to subscribers after drop or
add of columns. This problem occurs in SQL Server 2000 when Transact-SQL commands are used to
replicate changes to subscribers. To work around this problem, you should use the stored
procedures instead of Transact-SQL commands to replicate updates to the subscriber. To use
stored procedures instead of Transact-SQL commands, specify a stored procedure to execute at
the subscriber in the @upd_cmd parameter of the sp_addarticle system stored procedure.
You can use the sp_MSaddguidindex undocumented stored procedure to resolve
problems with rowguid column during adding articles.
You can encounter that the sp_addmergearticle system stored procedure causes the Snapshot Agent
to fail. This problem occurs when you add a merge article with a subset filter and the new article
does not have a rowguid column. To work around this problem, you should add a rowguid column to
the article by using the sp_MSaddguidindex stored procedure and then restart the Snapshot Agent.
You can use the sp_changearticle stored procedure to resolve problems with
an article's owner.
You can encounter that the sp_getqueuedrows stored procedure does not return any rows although
queued rows exist. This problem occurs because the subscribed table owner is incorrectly marked
as NULL in the MSsubscription_articles table. To work around this problem, you can use the
sp_changearticle stored procedure to change an article's owner.
Note. Changing an article's owner does not take effect until you reinitialize the subscription.
You can use the sp_adjustpublisheridentityrange stored procedure to resolve
problems with inserting to the publisher's table with identities.
Inserting to the publisher's table with identities may cause error 548 when the merge agent is
running in continuous mode and is using automatic identity range handling. To work around this
problem, you can use the sp_adjustpublisheridentityrange system stored procedure to adjust the
identity range manually.
The sp_addpullsubscription stored procedure can be used to resolve problems
with pull subscribers synchronization.
The named pull subscriptions may become unsynchronized with the published database when the
transactional publications were setting up using SQL Server Enterprise Manager. This problem
occurs when pull subscriptions are reinitialized from the subscriber. To work around this
problem, you can use the sp_addpullsubscription stored procedure instead of Enterprise Manager
and set the @immediate_sync property of pull subscriptions to be the same as the publication.
You can use the sp_replcounters stored procedure to resolve problems with
obtaining replication statistics information.
You can encounter that the replication statistics generated in the Logreader or Distribution
Agent's output file is incorrect. To work around this problem, you can use the sp_replcounters
stored procedure to obtain replication statistics information.
You can use the sp_addmergepublication stored procedure to resolve error 50007.
This error indicates that the query contains too many table names (the maximum allowable is 256).
This problem occurs in a merge publication that has a large number of published tables and join
filters when the "dynamic filtering" option is enabled in the publication. To work around this
problem, you can disable the dynamic filtering option in the merge publication by setting the
@dynamic_filters parameter to false in the sp_addmergepublication stored procedure.
The sp_repldone stored procedure can be used to resolve problems with the
transaction log truncation.
You can encounter that the transaction log of a database, with a snapshot publication, does
not truncate. This problem occurs when the database is also a subscriber to a transactional
replication publication. To work around this problem, you can run the sp_repldone stored
procedure in the database with the snapshot publication to update the record that identifies
the last distributed transaction.
You can use the sp_addsubscription stored procedure to resolve problems
with Distribution Agent termination.
The Distribution Agent termination may occur when you use transactional replication in a
republishing topology. In this case, the Distribution Agent experiences an access violation,
which leads to an abnormal termination. This problem occurs because the loopback detection
is not turned on for any articles. To work around this problem, you can specify that
@loopback_detection = 'true' by using the sp_addsubscription stored procedure.
