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 I/O operations are performed in Windows 7
Read More »
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 »
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 »
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 »
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 monitor SQL Transactional Replication and query against MSmerge_sessions table for Merge Replication. However, you can also create your own
Read More »