INF: Information that PSS needs to troubleshoot SQL Server replication

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.

Information Required for All Replication Cases

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 Base:
  233332  (http://support.microsoft.com/kb/233332/ ) 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 Online.
• 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. For additional information about how to configure this file, click the following article number to view the article in the Microsoft Knowledge Base:
  312292  (http://support.microsoft.com/kb/312292/ ) 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:
  298475  (http://support.microsoft.com/kb/298475/ ) 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 problem.
• 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.