Microsoft Product Support Services (PSS) needs certain
information when troubleshooting SQL Server replication cases. The purpose of
this article is to document the information PSS support professionals most frequently
request from customers when opening support cases. This is not, however, an
exhaustive list, and the PSS support professional working your case may request additional
information that is specific to your environment.
To successfully troubleshoot any replication case, PSS requests
the following information:
The most valuable information in troubleshooting any SQL
Server replication problem is a clear description of the servers that are
involved in your environment, and their role in your replication topology.
While PSS does not expect a written report that includes this information, be
prepared to discuss the servers involved and their roles (such as the
Publisher, the Distributor, or the Subscriber).
A SQLDIAG report from all servers that are involved in the
problem. This report includes all of the SQL Server error logs in addition to
basic information from your server, such as the output of the following stored
procedures: sp_helpdb, sp_who2, sp_lock, and sp_configure.
NOTE: There is a problem with SQLDIAG reports from clustered computers
that are running SQL Server. If your problem involves a clustered computer that
is running SQL Server, view the following article in the Microsoft Knowledge
INF: How to Run SQLDIAG on a Clustered/Virtual SQL Server
While the SQLDIAG report includes the SQL Server error
logs, the complete contents of the SQL Server \Log subfolder is also very
valuable. This folder not only includes the SQL Server Agent output files but
also any .dmp files that server exceptions or assertions generate.
Additionally, replication "exception logs" are stored here. Place all these
files in a .zip file, and then send the .zip file to PSS.
Save the System and Application event logs as text (.txt)
files from all the servers in your topology for the support professional that
is working your case. The support professional can use this information to
diagnose any potential operating-system level problems, such as network
connectivity or general hardware issues.
You can generate a Transact-SQL script of your replicated
environment that includes all of the SQL Server stored procedure calls that
were used to create the publications and articles in your topology. This
information helps immensely in obtaining more detail about your environment,
such as the number of articles, the ways in which your articles are configured,
and the properties of your publication. For detailed information about how to
generate the script, see the "Scripting Replication" topic in SQL Server Books
The exact error messages that the replication agents in
your topology are receiving are also extremely important in finding the correct
solution to your problem. In addition to getting the verbatim error message,
understanding the operations that lead up to the error this is occurring is
also very important. The best way to get this information is in the form of an
agent output file.
additional information about how to configure this file, click the following
article number to view the article in the Microsoft Knowledge Base:
HOW TO: Enable Replication Agents for Logging to Output Files
The replication agent for which you configure the
output file depends on your particular problem.
More Specific Troubleshooting Information
The following specific information is not required for all
replication support cases, but may lead to a faster resolution to your problem,
depending on the issue that you are seeing:
While replication is a fairly specialized component of SQL
Server, it is essentially a client application. As such, troubleshooting
replication performance problems typically start the same as basic application
performance issues; both require the same data. When you are working with PSS
on replication performance cases, use the following Knowledge Base article as a
guide for the information PSS that will request:
HOW TO: Troubleshoot Application Performance Issues
If you are having problems with transactional replication,
and the Distribution Agent is the source of your problem, you can connect to
the Distributor, and then run the sp_browsereplcmds stored procedure on the distribution database. The output of the sp_browsereplcmds stored procedure at the time of the failure can be extremely
useful to PSS in determining the potential cause of a Distribution Agent
failure. This stored procedure produces, in a readable format, all of the
commands in the distribution database that are waiting to be delivered to your
Subscriber (or Subscribers).
Alternatively, if the failure centers at
Log Reader Agent, the output of the sp_repltrans stored procedure is useful. This procedure outputs all of the
transactions in the Publisher's transaction log that are waiting to be
delivered to the distribution database.
If you can classify your problem as a data-type or object
schema-related issue, PSS can potentially use the Transact-SQL script that you
used to create this object to diagnose and to resolve the problem. You can
generate a Transact-SQL script of your database by right-clicking your database
in SQL Server Enterprise Manager, clicking All Tasks, and then clicking Generate SQL Script.
While SQL Profiler is most frequently used to troubleshoot
performance issues, PSS can also use simple SQL Profiler traces that you took
on the server at the time of the error to quickly pinpoint a particular
Transact-SQL statement or stored procedure that might be the source of your
If you are contacting PSS about a merge replication
conflict issue, include the output of the sp_helpmergeconflictrows or the sp_helpmergedeleteconflictrows stored procedure (or both). The output of these stored procedures
includes valuable information about the conflict that occurred. This stored
procedure is run on the computer where the conflict table is stored.