Technical: Microsoft – SQL Server – Change Data Capture – Error – When FileGroup does not contain datafiles

Technical: Microsoft – SQL Server – Change Data Capture – Error – When FileGroup does not contain datafiles

Introduction

Not easy to recover from creating a Change Data Capture and targeting an empty file group.

Create Database

If Database DBLAB, does not exist please create it

        if db_id('DBLab') is null
        begin

            exec ('create database [DBLab]');
            exec ( 'alter database [DBLAB] set recovery simple');
        end
        go

Add File Group

If fileGroup fileGroup_MSSQLServer_CDC, does not exist then create one.

        use [DBLab]
        go
	/*
		Check if File group does not exist, and if so create it
	*/
	if not exists
	(
		select *
		from   sys.sysfilegroups tblFileGroup
		where  tblFileGroup.groupname = 'fileGroup_MSSQLServer_CDC'
	)
	begin

		print 'Creating file group DBLab-fileGroup_MSSQLServer_CDC ... '

		ALTER DATABASE [DBLab] 
			add filegroup fileGroup_MSSQLServer_CDC

		print 'Creating file group DBLab-fileGroup_MSSQLServer_CDC'

	end

Enable Database for CDC

Enable Database for CDC


use [DBLab]
go
if not exists
(

      SELECT *
      FROM   sys.databases
      WHERE  is_cdc_enabled = 1
      and name= db_name()

)
begin

     print 'Enable CDC ' + db_name() + ' ...'

         EXEC sys.sp_cdc_enable_db

     print 'Enabled CDC ' + db_name() + ''

end

Create Object (Table)

Create Object



set nocount on
go

use [DBLAB]
go

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

/*
	DROP TABLE [dbo].[customer];
*/

set noexec off
go

if object_id('[dbo].[customer]') is not null
begin

	set noexec on

end
go

CREATE TABLE [dbo].[customer]
(

	  [CustomerID]   [bigint] NOT NULL

	, [CustomerName] [nvarchar](255) NOT NULL

	, [inceptionDate] [datetime] NOT NULL

	, [expiryDate] [datetime] NULL

	, [active] bit not null 
		constraint defaultActive default 1

	, [dateAdded]    [datetime] NOT NULL
		 constraint defaultDateAdded default getutcdate()

	, [addedBy]      [sysname]  NOT NULL 
		constraint defaultAddedBy default SYSTEM_USER

	 CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
		(
			[CustomerID] ASC
		)WITH 
		(
			  PAD_INDEX  = OFF
			, STATISTICS_NORECOMPUTE  = OFF
			, IGNORE_DUP_KEY = OFF
			, ALLOW_ROW_LOCKS  = ON
			, ALLOW_PAGE_LOCKS  = ON
		) 
		ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

set noexec off
go

Enable Object for CDC

Enable Object for CDC



set nocount on
go

use [DBLAB]
go

declare @objectSchema   sysname
declare @objectName     sysname
declare @objectNameFull sysname
declare @objectID       int
declare @filegroup_name sysname
declare @addCDC         bit

set @objectSchema = 'dbo'
set @objectName = 'customer'
set @objectNameFull = quoteName(@objectSchema) 
                                 + '.'
                                 + quoteName(@objectName)
set @objectID = object_id(@ObjectNameFull)
set @filegroup_name = N'fileGroup_MSSQLServer_CDC'
set @addCDC = 0
set @addCDC = 1

if (@addCDC = 0)
begin

	print 'CDC - Disable....' 

	if exists
	(

		SELECT *
		FROM   sys.tables 
		WHERE  is_tracked_by_cdc = 1
		and    schema_id = schema_id(@objectSchema)
		and    name= @objectName

	)
	begin

		EXEC sys.sp_cdc_disable_table
			  @source_schema = @objectSchema
			, @source_name   = @objectName
			, @capture_instance = 'dbo_customer'

	end

	print 'CDC - Disable' 

end

else if (@addCDC = 1)
begin

	if not exists
	(

		SELECT *
		FROM   sys.tables 
		WHERE  is_tracked_by_cdc = 1
		and    schema_id = schema_id(@objectSchema)
		and    name= @objectName

	)
	begin

	   print 'CDC - Enable....' 

	  EXEC sys.sp_cdc_enable_table
		  @source_schema = @objectSchema
		, @source_name   = @objectName
		, @role_name     = null -- N'roleMSQLServerCDC'

		/*
		  Msg 50000, Level 16, State 3, Line 129
		  Either a range parameter or the row filter option is not valid.

		   , @filegroup_name = N'fileGroup_MSSQLServer_CDC'
		*/

		--, @filegroup_name = N'PRIMARY'
		, @filegroup_name = @filegroup_name

	     select 
		  object_name(object_id) as objectName
		, capture_instance
		, supports_net_changes
		, index_name
		, filegroup_name
		, create_date
	     from   [cdc].[change_tables]
	     where  source_object_id =  @objectID

	     print 'CDC - Enabled' 

	end

end --addCDC =1

exec sys.sp_cdc_help_change_data_capture

Add Data

Add Data



USE [DBLab]
GO

set noexec off
set nocount off
go

/*
	Cannot truncate table 'dbo.customer' because it is published for replication or enabled for Change Data Capture.
*/
delete from [dbo].[customer]
go

INSERT INTO [dbo].[customer]
(
	 [CustomerID]
    ,[CustomerName]
    ,[inceptionDate]
    ,[expiryDate]
)
VALUES
(
	  1
   , 'Dell'
   , '2013-12-17'
   , null
)

INSERT INTO [dbo].[customer]
(
	 [CustomerID]
    ,[CustomerName]
    ,[inceptionDate]
    ,[expiryDate]
)
VALUES
(
	  2
   , 'Intel'
   , '2013-12-18'
   , null
)

INSERT INTO [dbo].[customer]
(
	 [CustomerID]
    ,[CustomerName]
    ,[inceptionDate]
    ,[expiryDate]
)
VALUES
(
	  3
   , 'Bart'
   , '2013-12-18'
   , null
)

INSERT INTO [dbo].[customer]
(
	 [CustomerID]
    ,[CustomerName]
    ,[inceptionDate]
    ,[expiryDate]
)
VALUES
(
	  4
   , 'Amazon'
   , '2013-12-18'
   , null
)

INSERT INTO [dbo].[customer]
(
	 [CustomerID]
    ,[CustomerName]
    ,[inceptionDate]
    ,[expiryDate]
)
VALUES
(
	  5
   , 'Belkin'
   , '2013-12-18'
   , null
)

INSERT INTO [dbo].[customer]
(
	 [CustomerID]
    ,[CustomerName]
    ,[inceptionDate]
    ,[expiryDate]
)
VALUES
(
	  6
   , 'Toyota'
   , '2013-12-18'
   , null
)

INSERT INTO [dbo].[customer]
(
	 [CustomerID]
    ,[CustomerName]
    ,[inceptionDate]
    ,[expiryDate]
)
VALUES
(
	  7
   , 'Ford'
   , '2013-12-18'
   , null
)

INSERT INTO [dbo].[customer]
(
	 [CustomerID]
    ,[CustomerName]
    ,[inceptionDate]
    ,[expiryDate]
)
VALUES
(
	  8
   , 'Nizzan'
   , '2013-12-18'
   , null
)

INSERT INTO [dbo].[customer]
(
	 [CustomerID]
    ,[CustomerName]
    ,[inceptionDate]
    ,[expiryDate]
)
VALUES
(
	  9
   , 'Compaq'
   , '2013-12-18'
   , null
)

update [dbo].[customer]
set  active = 0
where  [CustomerID] % 3 = 0

select *
from   [dbo].[customer]

Retrieve CDC Data



use [DBLAB]
go

DECLARE @begin_time datetime, @end_time datetime
declare @from_lsn binary(10), @to_lsn binary(10)
declare @save_to_lsn binary(10)
declare @min_lsn binary(10)
declare @capture_instance sysname
declare  @objectID int

-- Obtain the beginning of the time interval.
SET @begin_time = GETDATE() -1;
 -- Obtain the end of the time interval.
SET @end_time = GETDATE();

select 
         @from_lsn = start_lsn
       , @capture_instance = capture_instance
from   [cdc].[change_tables]
where  source_object_id =  @objectID

set @objectID = object_id('dbo.customer')

-- Map the time interval to a change data capture query range.
SET @save_to_lsn = 0x000000000000000000;
SET @from_lsn = sys.fn_cdc_map_time_to_lsn
                   ('smallest greater than or equal', @begin_time);
SET @min_lsn = sys.fn_cdc_get_min_lsn (@capture_instance);
SET @from_lsn = sys.fn_cdc_increment_lsn(@save_to_lsn);
-- Tests to verify the low endpoint is valid for the current capture instance.
IF (@from_lsn < @min_lsn)
    BEGIN
        RAISERROR('Low endpoint of the request interval is invalid.', 16, -1);
    END

SET @from_lsn = sys.fn_cdc_get_min_lsn(@capture_instance)
SET @to_LSN =sys.fn_cdc_get_max_lsn()

if (@from_lsn = 0x00000000000000000000)
begin

	select @from_lsn = start_lsn
	from   [cdc].[change_tables]
	where  source_object_id =  @objectID

end

select *
from   [cdc].[change_tables]

select 
	   @from_lsn as [from_lsn]
         , sys.fn_cdc_map_lsn_to_time(@from_lsn) fromTime
	 , @min_lsn as [min_lsn]
	 , @to_lsn as [to_lsn]
         , sys.fn_cdc_map_lsn_to_time(@to_lsn) toTime

begin try

    SELECT
       CT.__$operation
     , CT.__$update_mask
     , CT.customerID
     , CT.customerName
     , LSN.tran_begin_time
     , LSN.tran_end_time
     , sys.fn_cdc_has_column_changed 
          ('dbo_Customer', 'customerID', __$update_mask) AS is_customerID_changed
     , sys.fn_cdc_has_column_changed
          ('dbo_Customer', 'CustomerName', __$update_mask) AS is_customerName_changed
    , sys.fn_cdc_has_column_changed ('dbo_Customer', 'expiryDate', __$update_mask) 
          AS id_ExpiryDate_changed
    , sys.fn_cdc_has_column_changed ('dbo_Customer', 'dateAdded', __$update_mask) 
        AS id_dateAdded_changed
   , sys.fn_cdc_has_column_changed ('dbo_Customer', 'active', __$update_mask) 
       AS id_Active_changed

  FROM cdc.fn_cdc_get_all_changes_dbo_Customer(@from_lsn, @to_lsn, N'all') CT
  --FROM cdc.fn_cdc_get_net_changes_dbo_Customer(@from_lsn, @to_lsn, N'all') CT

	inner join cdc.lsn_time_mapping AS LSN

		 ON CT.__$start_lsn = LSN.start_lsn

  WHERE __$operation in ( 3, 4)

end try

BEGIN CATCH

     DECLARE

        @ERROR_SEVERITY INT,

        @ERROR_STATE INT,

        @ERROR_NUMBER INT,

        @ERROR_LINE INT,

        @ERROR_MESSAGE VARCHAR(245)

    SELECT

        @ERROR_SEVERITY = ERROR_SEVERITY(),

        @ERROR_STATE = ERROR_STATE(),

        @ERROR_NUMBER = ERROR_NUMBER(),

        @ERROR_LINE = ERROR_LINE(),

        @ERROR_MESSAGE = ERROR_MESSAGE()

     IF @ERROR_NUMBER = 313

            RAISERROR(

                 'Either a range parameter or the row filter option is not valid.', 

                 @ERROR_SEVERITY, @ERROR_STATE)

     ELSE

     IF @ERROR_NUMBER = 229

            RAISERROR(

                 'The caller is not authorized to perform the query.', 

                 @ERROR_SEVERITY, @ERROR_STATE)

     ELSE

            RAISERROR('Msg %d, Line %d: %s',

                 @ERROR_SEVERITY, @ERROR_STATE, @ERROR_NUMBER,

                 @ERROR_LINE, @ERROR_MESSAGE) 

END CATCH 

Output – Grid:

CDCMetadata

Output – Text:

Text

Msg 50000, Level 16, State 3, Line 125

Either a range parameter or the row filter option is not valid.

GUI

CDCMetadata_Output_Text

Debugging Steps

The error message “Either a range parameter or the row filter option is not valid” is a bit generic.

Debugging Steps – Check Error Log

Debugging Steps – Check Dynamic Management View (DMV)

Debugging Steps – Check Dynamic Management View (DMV) – sys.dm_cdc_errors

The sys.dm_cdc_errors dmv has good error message.


select *
from  sys.dm_cdc_errors
order by session_id desc;

Output:

CDCMetadata_DMCDCErrors

Recovery Steps?

Use Dynamic Management Views

Friend Dynamic Management Views (dmv).  In this case:

Recovery Steps – Create File under file group

Let us go create a file for out designated file group.


declare @fileCreate bit

set @fileCreate = 1
if (@fileCreate = 1)
begin

	if not exists
	(
		select 
   		        tblFileGroup.groupid
		      , tblFileGroup.groupname
		      , tblFile.fileid
		      , tblFile.name
		      , tblFile.filename
		from   sys.sysfilegroups tblFileGroup

			  inner join sys.sysfiles tblFile
				on tblFile.groupid = tblFilegroup.groupid

		where tblFileGroup.groupname = 'fileGroup_MSSQLServer_CDC'
	)

	begin

	   print'File Create - ChangeDataCapture_20131217 ....'

	   ALTER DATABASE [DBLab] 
	   ADD FILE
	    ( 
  	          NAME = N'ChangeDataCapture_20131217'
	        , FILENAME = N'D:\Microsoft\SQLServer\Datafiles\CDC_20131217.ndf'   
                , SIZE = 3072KB 
	        , FILEGROWTH = 1024KB 
	    ) 
	   TO FILEGROUP [fileGroup_MSSQLServer_CDC]

	   print'File Created - ChangeDataCapture_20131217'

	end

end

Re-Check CDC Tables

Once file for designated file group is created, checked CDC but things are still no good!

 

Diagnostic Tools

Check CDC /Change-Tables, FileGroup, and Files

Here is a SQL for checking CDC File Groups mapping



use [DBLab]
go

select 

		  object_name(tblCDCTableChange.object_id) as objectName
		, tblCDCTableChange.capture_instance
		, tblCDCTableChange.start_lsn
		, tblCDCTableChange.end_lsn
		, tblCDCTableChange.filegroup_name
		, tblCDCTableChange.create_date as CDCCreateDate
		, tblFileGroup.groupname as fileGroupName
		, tblFile.filename as fileName

from   [cdc].[change_tables] tblCDCTableChange

   left outer join sys.sysfilegroups tblFileGroup
      on
        (

          (tblCDCTableChange.filegroup_name = tblFileGroup.groupname)    

         )

   left outer join sys.sysfiles tblFile
		on tblFileGroup.groupid = tblFile.groupid

Summary

In summary, read has much as you can, write detailed deployment steps, and investigate diagnostic paths; i.e dynamic management views.

References

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s