New SQLskills Pluralsight courses

We’ve released a couple of new Pluralsight courses recently:

The first is the conclusion of Glenn’s DMVs course series, SQL Server 2014 DMV Diagnostic Queries – Part 3, based on the very popular set of DMV queries he publishes every month.

The second course is Jonathan’s long-awaited SQL Server: Change Data Capture course. The short abstract is:

Learn how to capture SQL Server table data changes for downstream consumption using the Change Data Capture feature of SQL Server. This course is applicable to developers, DBAs, and architects on SQL Server 2008 onward.

The module list is:

  • Introduction
  • Configuring Change Data Capture
  • Querying Change Data Using Transact-SQL
  • Administration
  • Performance Tuning and Optimization
  • SQL Server 2012 SSIS Components

Jonathan’s been using CDC quite a bit with clients recently and this course is a distillation of all his knowledge and experience.

Check them both out!

We’ve got a lot more courses in production and planned for the remainder of 2015 around server consolidation, Service Broker (3 courses planned), more stored procedure performance, fragmentation, and more. Watch this space (and the Insider Newsletter) for details.

Thanks!

REPLICATION preventing log reuse but no replication configured

Last week, for the second time in as many weeks, I was sent a question in email from someone who had a transaction log that was growing out of control. He’d already queried log_reuse_wait_desc for the database (see this post for some more background) and the result was REPLICATION.

The problem was, there was no replication configured for that database. Just for completeness, he tried turning off the publish and merge publish settings for the database with sp_replicationdboption, ran sp_removedbreplication on the database, and then when those didn’t work, he also tried configuring and then removing replication. Nothing worked and the transaction log kept growing.

The problem turned out to be Change Data Capture. CDC uses the replication log scanning mechanism to harvest changes from the database, either piggy-backing on replication’s Log Reader Agent job or creating it’s own capture job if replication isn’t configured. If CDC is configured but the capture job isn’t running, the log_reuse_wait_desc will show as REPLICATION, as the log manager doesn’t have any way to know *why* the replication log scanner is configured, just that it is, and it hasn’t run.

So, if you ever see REPLICATION as the log_reuse_wait_desc and don’t have replication configured, check the is_cdc_enabled flag in sys.databases too. And then either figure out why the CDC capture job isn’t running correctly (see Change Data Capture Agent Jobs in this BOL entry), or remove CDC if it’s not supposed to be there (see this BOL entry).

Hope that helps a few people!

Vacation…

Don't expect any activity here for at least a couple of weeks as we're leaving today for a long-overdue dive trip (on the Belize Aggressor) – our first diving for almost 18 months. I'll post lots of photos in December…

In the meantime, please go run the index survey code from my last post – the more data the better!

Cheers