When to use SQL Server Change Data Capture

There’s a number of things to consider in deciding on Change Data Capture (CDC) and deciding on the ideal use cases for CDC is very much dependant on the specific requirements. So it is important to understand in detail the project requirements. This should be weighed up against other solutions such as Triggers Some initial thoughts for discussion: For Auditing – CDC is an appropriate tool , as it can present the complete history of the data. Deciding on whether you’d rather use triggers will depend on reviewing the data in the change table and assessing whether it’s appropriate for... Read more →

Should I use SQL Change Data Capture for data audit ?

Question: There is lots of information around the SQL Server Change Data Capture (CDC) feature, and I’m trying to decide if CDC is suitable for the purposes of a project which requires data auditing. We currently have a sql server triggers based solution in place for capturing data changes on certain tables. Could you outline some the issues to consider when deciding if CDC is an appropriate tool. Answer: Deciding on when to adopt CDC is dependant on the specific requirements. The first step is to understand in detail the project requirements. As you mentioned in your question, there are... Read more →

How to create a table in another FILEGROUP using SELECT INTO

Question: I am trying to run a bulk load with the SELECT INTO method. Normally when using the SELECT INTO method it is possible to automatically create the table. But is it possible to create the new table in another filegroup during the SELECT INTO process? The reason is I don’t have enough disk space on the PRIMARY filegroup disk , therefore need to redirect the new table onto a new a new FILEGROUP set up on a new disk . Answer: It is not possible to create a new table in a non default FILEGROUP as part of the... Read more →

Fix: –NESTING_TRANSACTION_FULL wait type and how to reduce it

Question: There was a failure on a regular SQL Server job. There are now repeated errors focused on NESTING_TRANSACTION_FULL. Double checked all usual resource indicators such as CPU,IO,Memory and all appear to be within normal boundaries Timeout occurred while waiting for latch: class 'NESTING_TRANSACTION_FULL', I checked the waiting tasks and discovered the wait type as LATCH_EX with resource description as : NESTING_TRANSACTION_FULL (0000000E64CA80F8) Answer:This is a tough nut to crack. I’ve experienced this problem in the past under different circumstances. These are some of the methods I’ve used to solve or alleviate the symptoms. Without full understanding whether it was... Read more →

Error 16955 – Could not create an acceptable cursor . Dynamic cursors and DISTINCT KEYWORD

Question: I’m getting an error when issuing some t-sql statements . The error details are : Msg 16955, Level 16, State 2, Line 1 Could not create an acceptable cursor. Msg 16945, Level 16, State 2, Procedure sp_cursoropen, Line 1 The cursor was not declared. The code causing the error code declare @p1 int set @p1=NULL declare @p3 int set @p3=229378 declare @p4 int set @p4=294916 declare @p5 int set @p5=NULL exec sp_cursoropen @p1 output,N'SELECT DISTINCT itemid FROM ShopItems Inner Join Categories on itemid =categoryid’ ,@p3 output,@p4 output,@p5 output select @p1, @p3, @p4, @p5 What is causing this error and... Read more →