Unexplained slowness in SQL 2008 on Windows 2008 R2

Chris Skorlinski Microsoft SQL Server Escalation Services A customer called today about an unexplained slowness in SQL Server 2008 on Windows 2008 R2.  All SQL data looked great, no blocking, no obvious IO bottlenecks, just overall slow performance. 976700  An application stops responding, experiences low performance, or experiences high privileged CPU usage if many large…


Troubleshooting LogReader Timeout executing sp_replcmds

Chris Skorlinski Microsoft SQL Server Escalation Services SQL Server Transaction LogReader Agent timeout executing sp_replcmds occurs most often when a large transactions has been written to the Published database transaction log, or there are a large number of un-replicated commands.  Either problem requires the LogReader to read more log entries than the timeout parameter allows…


Published Database Transaction Log continues to Grow!

Chris Skorlinski Microsoft SQL Server Escalation Services 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’)…


Determine Transactional Replication workload to help resolve data latency

Chris Skorlinski Microsoft SQL Server Escalation Services 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…


Walk through of Microsoft SQL Server Change Data Capture

Walk through of Microsoft SQL Server Change Data Capture Chris Skorlinski Microsoft SQL Server Escalation Services 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…


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

How to find “waiting” STATEMENT in Microsoft SQL Server stored procedure 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…


How to synchronize SQL Express subscribers using VBA

How to synchronize SQL Express subscribers using VBA Chris Skorlinski SQL Server Escalation Services 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…


How to monitor the Health of SQL Server Replication

Technorati Tags: Performance How to monitor the Health of SQL Server Replication Chris Skorlinski Microsoft SQL Server Escalation Services Today someone asked me how to “roll their own” SQL Replication Monitor to keep an eye on the health of SQL Server Replication.  You’ve seen from my previous post I usually use a TracerToken job to…


SQL Server 2008/R2 Audit Triggers for Change Data Capture changes_tables table.

SQL Server 2008/R2 Audit Triggers for Change Data Capture changes_tables table — — FILENAME: CDCchange_tablesAudit.SQL — — AUTHOR:  Chris Skorlinski — — DESCRIPTION: SQL Server 2008/R2 Audit Triggers for Change Data Capture changes_tables table. — This script is used to audit the activity in the changes_tables table created when –  you enable Change Data Capture…