Year: 2010

Published Database Transaction Log continues to Grow!

Chris Skorlinski [MSFT] Troubleshooting October 24, 2010

A customer recently noticed their Published database transaction log would continue to grow until they backed up the Distribution database. DBCC OPENTRAN() Oldest distributed LSN : (0:0:0) Oldest non-distributed LSN : (982:100898:1) --> 0x000003D6 : 00018A22 : 001 -- Commit Tran(sp_replshowcmds) 0x000003D6:00018A22:0004 select [Current LSN],[Operation],[Transaction ID], Left([Description],20) from::fn_dblog('0x000003D6:00018A22:001','0x000003D6:00018A22:0004') “… to clear out the Transaction Log
Read More »

Determine Transactional Replication workload to help resolve data latency

Chris Skorlinski [MSFT] Best Practices, Inside Replication, Try This October 19, 2010

Latency problems are often caused when series of transactions are trying to move a large batch of commands from the Publisher to the Distributor to the Subscribers. The queries below show overall rows counts and index statistics for commands stored in the Distribution database. Use the results of these queries to look for unexpected high-volume
Read More »

Walk through of Microsoft SQL Server Change Data Capture

Chris Skorlinski [MSFT] ReplTalk Blog September 30, 2010

This sample script walks through some of the Microsoft Change Data Captures functions. Copy and past the script into SQL Server Management Studio and execute each command. Sample output is included in the script. This demo is based on the [AdventureWorksLT2008] database USE [AdventureWorksLT2008] GO -- Create Sample table used for CDC Demo CREATE TABLE
Read More »

How to find “waiting” STATEMENT inside Microsoft SQL Server stored procedure

ReplTalk Troubleshooting September 27, 2010

Content provided by: Ajith Krishnan Microsoft SQL Server Escalation Services I was working with Ajith the other day to track down a blocking problem for a customer. We know the “problem” stored procedure but wanted a quick way to determine which statement inside the stored procedure were being blocked. Ajith wrote the query below using
Read More »

How to synchronize SQL Express subscribers using VBA

ReplTalk Inside Replication September 22, 2010

Question to research: How can subscribers with just VBA do an “on demand synchronization”? Background: SQL 2008 R2 Standard Edition Publisher/Distributor with SQL 2008 R2 Express subscribers. SQL Express does not include SQL Agent to schedule Replication jobs, therefore, the subscribers are setup using “push” Replication from the Publisher. They would like the ability for
Read More »