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
This blog contains information related to Microsoft SQL Server and Oracle Administration: Installation, Configuration, Maintenance and Troubleshooting.
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:
6/15/2012
Change Data Capture "Violation of PRIMARY KEY" issue
The following error appeared in one of my cdc job’s history today:
1. Stop and disable cdc.DBNAME_capture job
2. Execute the following script:
4. Execute the following script:
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 GO3. 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)
Subscribe to:
Posts (Atom)