Category: Troubleshooting

Published Database Transaction Log continues to Grow!

Chris Skorlinski [MSFT] Troubleshooting October 24, 2010

Share this PostA 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
Read More »

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

ReplTalk Troubleshooting September 27, 2010

Share this PostContent 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
Read More »

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

Chris Skorlinski [MSFT] Inside Replication, Troubleshooting August 31, 2010

Share this PostSQL 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
Read More »

Tips for troubleshooting SQL Agent Error: 15281

Chris Skorlinski [MSFT] Troubleshooting August 18, 2010

Share this PostWe encountered an interesting problem the other day I thought I would post on ReplTalk. Customer call when they noticed the SQL Server Agent resources in their SQL 2008 clustered installation would not start. The following error appeared in the Windows Application and System Event log. Windows Application Event Log SQLServerAgent could not
Read More »

How to change the SQL Replication Agent History Cleanup Job

Chris Skorlinski [MSFT] Inside Replication, Troubleshooting August 17, 2010

Share this PostBy default the SQL Replication Agent History cleanup job runs every 10 minutes and deletes any historical data older the 48 hours. If you’re only running SQL Server Replication Agent a few times each day, you can reduce the frequency of this cleanup job and increase the retention period for historical data without
Read More »