How to resolve when Distribution Database is growing huge (+25gig)

Yes, I know, huge database is kind of relative, but generally if you see Distribution database growing more the 25gig it means the Cleanup processes is having a hard time deleting replicated transactions.  I’ll cover the how and why on Cleanup processes later, but for now I wanted to post a technique we’ve used to…

3

Troubleshooting Distribution Agent message code 8144. Procedure or function sp_MSupd_Address has too many arguments specified.

I thought it would be helpful to post some Replication Distribution Agent troubleshooting steps to help you not only understand more about Replication components, but to also show how I approach Replication problems. Problem: SQL 2005 Distribution Agent was failing with following messages 2009-12-29 00:17:53.917 Agent message code 8144. Procedure or function sp_MSupd_dboAddress has too…

0

Troubleshooting Snapshot Agent hang

I recently got a customer call where the SQL 2008 Snapshot Agent appeared to hang when started from SSMS. As you can see, pop-up status window shows “Starting agent”.  So, looks like its running, but it stays this way forever. Replication Monitor also shows as “Running”. If I double-click the I can zoom in on…

2

How to skip a transaction in SQL 2005/2008 Transactional Replication

Once in a while you may find the need to skip an indiviual transaction in SQL 2005/2008 Transactional Replication. For example, when Distribution Agent fails because of an error and the database integrity won’t be impacted by skipping that transactions.  For instructing the Distribution Agent to skip a transaction, SQL provides 2 very helpful stored procedures.  sp_helpsubscriptionerrors…

1

How to run TableDiff utility for ALL replicated (published) tables in SQL 2005 or SQL 2008

We recently need to find all the data which was different in 300 SQL Replicated tables on a pair of database.  To find the differences, we were planning on using the TableDiff utility included with SQL 2005.  TableDiff Utility http://msdn.microsoft.com/en-us/library/ms162843.aspx By default, the TableDiff creates a new *.SQL file for each table.  There is not…

1

How Replication setting Immediate_sync may cause Transactional Replication Distribution database growth

How Replication setting Immediate_sync may cause Transactional Replication Distribution database growth and long running cleanup. Distribution Cleanup for highly active servers with 60-100+ millions rows cached, database growth and cleanup will be an ongoing issue.  If we can reduce the number of transactions/commands cached in the distribution database we can improve overall replication performance.  …

0

Why is query performance of Replication Subscriber slower than Publisher?

  Time to compare indexes on Publisher vs. Subscriber.   We encounter an unusual problem in SQL 2005/2008 where the indexes didn’t get created on the Subscriber when the Snapshot was pushed by the Merge Agent.  Without critical indexes, query optimizer was forced to perform Table Scan.  Solution was easier, add the missing indexes.  The…

0

Transactional Replication Conversations

Transactional Replication Conversations When troubleshooting Transactional Replication performance break down the flow of data into 4 simultaneous conversation.  Examine performance of each conversation.  This will help verify where the bottleneck investigation should begin.       1)      LogReader Reader thread is reading the Transaction Log via stored procedure sp_replcmds, a wrapper for xp_replcmds.  It scans…

3

Troubleshooting Slow Distribution Agent performance in SQL 2005/2008

When troubleshooting SQL 2005/2008 Distribution Agent performance, first determine if the Distribution Agent Latency is from the Reader Thread retrieving rows from the DistributionDB or the Writer Thread writing to the subscriber.  Modify the Agent startup parameters to include the -OUTPUT with agent stats.   ************************ STATISTICS SINCE AGENT STARTED ***********************05-01-2009 17:35:54 Total Run Time…

0

How to create a SQL Server link server with a different name.

If select SQL linked server in the GUI you can’t specify a different name for the SQL Server.  You can only use the SQL host name as the Link Server name.   Here is an example of creating a Linked Server via script to specify link server name myGX620 for the SQL Server host name…

0