Replication Error ALTER Table Conflicted with the foreign key

Error:
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "XXXX_ABCD_fk". The conflict occurred in database "TESTDB", table "dbo.TEST_event", column 'event_id'. (Source: MSSQLServer, Error number: 547)
Get help: http://help/547

Or

The DELETE statement conflicted with the REFERENCE constraint "XXXX_ABCD_item_fk". The conflict occurred in database "TESTDB", table "dbo.TEST_hist", column 'offer_item_id'.

I was facing above error, After reintialization in SQL Server replication on TESTDB.
I tried to find forigen key XXXX_ABCD_fk but it was deleted few days and it was not found in database.
To resolve this issue required to delete "MSsavedForeignKeys" forgein key in table at subscription database.


delete dbo.MSsavedForeignKeys where constraint_name = N’XXXX_ABCD_item_fk’

Latter replication SYNC worked fine for me.

Connecting to the Integration Services service on the computer failed with the following error: "Access is denied"

Trying to connect SSIS 2016 can’t be connected from on SQL v17.0 tools. SQL 17 (14.0.17099.0) release of SSMS has known issues that connect to Integration Services started to face below error.

Error –Description: Connecting to the Integration Services service on the computer 

failed with the following error: "Access
is denied".

I tried local installation start SSMS with "Run as Administrator” Also tried connecting SSIS as “LOCALHOST” when I tried connecting as “LOCALHOST” below is the error I faced.

Error –Description: Connecting to the Integration Services service on the computer "LOCALHOST" failed

with the following error: "The Specified service doesn’t exist as an installed service”.

Solution:  I was able to connect to SSIS  by installing Microsoft SQL Server Management Studio - 16.5.3 as work around.

This article explains .  https://docs.microsoft.com/en-us/sql/integration-services/service/integration-services-service-ssis-service#manage-the-service

Removing Registered Publishers From Replication Monitor

If you have registered more publishers in replication monitor client tool, you can remove those by renaming\deleting rmsetting.xml file more over deleting each manually.

File can be found in below path:

For SQL Server 2005 Version.
C:\Documents and Settings\<Profile ID>\Application Data\Microsoft\Microsoft SQL Server\90\Tools\SQL Monitor.

For SQL Server 2008R2 Version.
C:\Documents and Settings\<Profile ID>\Application Data\Microsoft\Microsoft SQL Server\100\Tools\SQL Monitor.

For SQL Server 20012 Version.
C:\Documents and Settings\<Profile ID>\Application Data\Microsoft\Microsoft SQL Server\110\Tools\SQL Monitor.

Note : You are Removing only Registered Publishers From Replication Monitor Tool.

MSP Error: 1260 - Applying Service pack on SQL Server 2005.

Error Details

Error Number              : 1260
Error Description         : MSP Error: 1260  Windows cannot open this program because it has been prevented by a software restriction policy. For more information, open Event Viewer or contact your system administrator.

I too found missing file in below path as well.

C:\Program Files (x86)\Microsoft SQL Server\90\Setup Bootstrap\Cache\SQLSupport\x86\1033\SqlSupport.msi

1.Go to the Add or Remove Programs section of Control Panel and remove the SQL Server Setup Support Files.

2.Extract service pack file
Ex: SQL Server 2005 SP4 packages to C:\SQLServer2005_SP4\SP4 as follows
SQLServer2005SP4-KB2463332-x86-ENU /x:C:\SQLServer2005_SP4\SP4

3.Install SQL Server Setup Support .msi file of SP4 from extracted path below.
C:\SQLServer2005_SP4\Sp4\hotfixsqlsupport\files.

4.Now apply service pack on SQL Server,It completes successfully.

Error: ACO5400E The Virtual Device Interface is not registered with the Common Object Model

Problem – Unable to perform backup by using TDP For SQL Server tool on windows 2008\ R2 standard edition for SQL Server.

Error: ACO5400E The Virtual Device Interface is not registered with the Common Object Model.

Solution :

Volume Shadow Copy Service(VSS) is a set of Component Object Model (COM) application programming interfaces (APIs) that provides standardized interfaces, enabling third-party backup and restoration software to centrally manage the backup and restore operations on a variety of applications. VSS also implements a framework that enables volume backups to be performed while applications on a system continue to write to the volumes.

1.       Start “Volume Shadow Copy” services on server.

2.       Check SQL Server VSS Writer is up and running fine, else please start.

3.       If  SQL Server 2000 is installed on windows 2008 or R2, Install  SqlWriter.msi(can be found from any SQL 2008 or 2008R2 software) which creates SQL VSS writer services,

          VSS writer service which is much more required on windows 2008 or R2.For third party backup tools.

Sqlwriter.msi Can be found in \1033_ENU_LP\x64\Setup\x64(similar path for 32 bit) for 2008R2 and CD1 or servers\Setup folder for 2005

Deferred Transaction in SQL Server

I was unable to access few tables in a database, I noticed a error after executing DBCC CHECKDB with and Without TABLOCK. The database size was around 4 Gb(data file size 1.5Gb and log file 2.5Gb).

Msg 7929, Level 16, State 1, Line 1

Check statement aborted.  Database contains deferred transactions.

What are deferred transaction?

In SQL Server 2005 Enterprise Edition and later versions, Deferred transaction is a transaction that is uncommitted when the roll forward phase finishes and has encountered an error, its not possible to rollback as well because the transaction cannot be rolled backup and is deferred. Basically  a corrupted transaction can become deferred if data required by rollback (undo) is offline during database startup.

Database was  not allowing to perform any alter operation,.

 Executed the CHECKPOINT command against the database 'xyz'. Which failed with the below error message.

Msg 845, Level 17, State 1, Line 4

Time-out occurred while waiting for buffer latch type 4 for page (1:23212), database ID 34.

Identifying Deferred Transactions

Use dmv sys.dm_tran_locks and found that the query request_id was -3.

A value of -3 indicates that the request belongs to a deferred recovery transaction, such as, a transaction for which a rollback has been deferred at recovery because the rollback could not be completed successfully.

select * from sys.dm_tran_locks where request_session_id=-3

Solution:

Option 1: Restart SQL Server. If the problem was transient, the database should start without deferred transactions.

Option 2: If the transactions were deferred because a filegroup was offline, bring the filegroup back online.

To bring an offline filegroup back online, use the following Transact-SQL statement:

RESTORE DATABASE database_name FILEGROUP=<filegroup_name>

Option 3: Restoring database with good backup.

Option 4: If you don’t have backup

Copy mdf file use 'sp_attach_single_file_db’ and attach has different database name, Once after attaching run check db if everything is fine. Drop old database and rename existing database. 

Option 5 : Reset suspect status  then put database into emergency mode by executing the following Transact-SQL statement:

 ALTER DATABASE <database_name> SET EMERGENCY

Then, repair the database by using the DBCC REPAIR_ALLOW_DATA_LOSS option in one of the following DBCC statements: DBCC CHECKDB, DBCC CHECKALLOC, or DBCC CHECKTABLE.

Note: Using repair  allow loss checkdb database command results in data loss

Transactional Replication - Source: MSSQLServer, Error number: 18805

Error messages:
The process could not execute 'sp_replcmds' on 'D0XXXXxxx\SQL01'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20011)
Get help: http://help/MSSQL_REPL20011
The Log Reader Agent failed to construct a replicated command from log sequence number (LSN) {000c5636:000035af:0008}. Back up the publication database and contact Customer Support Services. (Source: MSSQLServer, Error number: 18805)
Get help: http://help/18805
The process could not execute 'sp_replcmds' on 'D0XXXXxxx\SQL01'. (Source: MSSQL_REPL, Error number: MSSQL_REPL22037)
Get help: http://help/MSSQL_REPL22037


Generally, the SQL Server Database Engine prevents gaps in the sequence of log backups, keeping the log chain intact but due to various reasons log to be broken
More often, there are various human actions, which can lead to broken log or taking backups using Truncate only(does not work in SQL versions 2008 and above) or no_log options(does not work in SQL versions 2008 and above).

To fix this re-constrution of the chain, you need to take a full or differential backup and then continue taking log backups.


2.used sp_repldone to mark every transaction in the log to be marked as replicated to the distribution database.

exec sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1


3.By using sp_replflush flushed Transaction Log article cache

4.Executed the sp_replrestart system stored procedure in the publication database without any parameters. This procedure forces replication to continue
even if the distributor and some subscribers have data that the publisher no longer has.

Note Make sure that there is no incoming traffic to the publisher before you perform this step.

Thus above error can be fixed.Even drop and re-create publisher\subscriber also fixes issue.