Distribution Agent Errors _OS Error

Error 1:

The process could not read file T:\ Microsoft SQL Server\\MSSQL\repldata\\ due to OS error 3. (Source: MSSQL_REPL, Error number: MSSQL_REPL20016)

Error 2:

Cannot DROP TABLE 'dbo.PrFlagAon' because it is being referenced by object 'VW_FlsAsoU2'. (Source: MSSQLServer, Error number: 3729)

Error 3:

One or more BLOB columns could not be sent to the server, attempt to recover from the problem failed.

Resolution1:

The error occurs in pull subscriptions because the remote distributor you must specify a UNC network share such as \\\snapshot rather than a local path for the snapshot folder. 

To alleviate this issue, make the snapshot folder a UNC network share and update the Distributor Properties to reflect the change the path.

Drop and recreate or reinitialize subscription with new snapshot.

Resolution 2:

The view is a schema bounded and which is creating the problem. Login to subscriber go to the database script out the view. Once you have script drop the view until it applies the snapshot on the subscriber.

Once applying snapshot is completed and replication applies all scripts re-create the views which are dropped in the above step.

Resolution 3:

This will fix automatically as the data is BLOB and if any network packet drop outs will cause this. If this a IMAGE type and failing each time, then MSDN says that to replicate LOB data, using "Distribution Profile for OLEDB streaming" profile is optimized.

The subscription(S) have been marked inactive and must be reinitialized.

Error Message:

Replication-Replication Distribution Subsystem: agent DBA\DBA-EPDW-EPDW-DB6C\DB6C-5 failed. The subscription(S) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated.

               

At publisher:

============

use distribution

go

STEP 1: select * From distribution..MSsubscriptions

P.S:  Note down publisher_id, publisher db name, publication_id , subscriber_id and subscriber_db name of whose status is 0

Status of the subscription: 0 = Inactive; 1 = Subscribed; 2 = Active

subscription_type 0=PUSH, 1=PULL

STEP 2:  Update Status to 2

if exists (select 1 from distribution..MSsubscriptions where status = 0)

begin

UPDATE distribution..MSsubscriptions

SET STATUS = 2

WHERE publisher_id = '--publisher_id -- will be integer --'

    AND publisher_db = '--publisher db name ---'

    AND publication_id = '--publication_id -- will be integer --'

    AND subscriber_id = '--subscriber_id -- will be integer ---'

    AND subscriber_db = '-- subscriber_db ---'

end

else

begin

print 'The subscription is not INACTIVE.. you are good for now .... !!'

end

STEP 3: Right click on subscriber and choose view synchronizing status

STEP 4: Click Start

STEP 5: Observe the replication monitor for any issues, if no replication is in Sync.

To review pending commands to replicate. At publisher:

========================================================

use distribution

go

exec sp_browsereplcmds

KILL permissions on SQL SEREVER

Here You Go:

Example:

USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'testuserpassword' 
GO
GRANT VIEW SERVER STATE TO TestUser
GO
GRANT ALTER ANY connection TO TestUser
GO