Showing posts with label sys.dm_cdc_errors. Show all posts
Showing posts with label sys.dm_cdc_errors. Show all posts

7/19/2012

Script deployment to multiple servers

I use the following script in order to deploy a T-SQL script to multiple servers:

SET NOCOUNT ON
GO

DECLARE @object_name SYSNAME                      
DECLARE @cmdstr NVARCHAR(300)                        
DECLARE @servername SYSNAME                             
DECLARE @instancename SYSNAME                      
DECLARE @inputfile NVARCHAR(128)                      
DECLARE @outputfile NVARCHAR(128)                     
DECLARE @prodsrvs TABLE(srvname sysname)
                
DECLARE crs_srv CURSOR
LOCAL 
FAST_FORWARD
READ_ONLY
FOR 
  SELECT srvname
  FROM @prodsrvs

INSERT INTO @prodsrvs(srvname)
SELECT 'Server-01'
UNION ALL SELECT 'Server-02'
UNION ALL SELECT 'Server-03'
UNION ALL SELECT 'Server-04'

OPEN crs_srv
FETCH NEXT FROM crs_srv INTO @servername

WHILE @@FETCH_STATUS=0                   
BEGIN

SELECT @cmdstr = 'sqlcmd -S ' + @servername + ' -i "\\PathToScript\Script.sql" -o "\\PathToScript\script_output.txt" '

EXEC dbo.xp_cmdshell @cmdstr
PRINT(@servername)

WAITFOR DELAY '00:00:05'

FETCH NEXT FROM crs_srv INTO @servername               
END

CLOSE crs_srv
DEALLOCATE crs_srv

SET NOCOUNT OFF
GO

6/15/2012

Change Data Capture "Violation of PRIMARY KEY" issue

The following error appeared in one of my cdc job’s history today:
Unable to add entries to the Change Data Capture LSN time mapping table to reflect dml changes applied to the tracked tables. Refer to previous errors in the current session to identify the cause and correct any associated problems.
When I’ve checked the sys.dm_cdc_errors there were Violation of PRIMARY KEY errors:
Violation of PRIMARY KEY constraint 'lsn_time_mapping_clustered_idx'. Cannot insert duplicate key in object 'cdc.lsn_time_mapping'
 I found a solution on sqlservercentral.com and decided to share it. So if you're facing this issue you can execute the following steps to fix it:

1. Stop and disable cdc.DBNAME_capture job
2. Execute the following script:
SELECT * INTO bkp_lsn_time_mapping FROM cdc.lsn_time_mapping
GO
TRUNCATE TABLE cdc.lsn_time_mapping
GO
3. Enable and start the job
4. Execute the following script:
INSERT INTO cdc.lsn_time_mapping
SELECT * FROM bkp_lsn_time_mapping
WHERE start_lsn not in (SELECT start_lsn FROM cdc.lsn_time_mapping)