“An error was reported while committing a database transaction but it could not be determined whether the transaction succeeded or failed on the database server“-Always on Availability groups

server1(xyz location)-------1GB(N/W)--------server1(abc location)

We have Always on Availability groups Synchronous Configured between server1 and server2 (both got SQL Server 2014 installed)

No issues happened, and we had automatic failover happened without any data loss.

 

server1(xyz location)-------100MB(N/W)--------server2(Colocation)

As soon as we moved to Colocation to a 100MB network between two servers, issues started happening

 “An error was reported while committing a database transaction but it could not be determined whether the transaction succeeded or failed on the database server“

Ref:

https://msdn.microsoft.com/en-us/library/dn135335(v=sql.110).aspx

Synchronous mode :Transaction should be committed on Secondary and send ACK to primary for commit(Automatic Failover)

Asynchronous mode : Transaction  should be committed on Primary and  send ACK to Secondary(Manual Failover)

“An error was reported while committing a database transaction but it could not be determined whether the transaction succeeded or failed on the database server“-Always on Availability groups

FPVI-SQL1HA(1595)-------1GB(N/W)--------FPMC-SQL1HA(1568)

We have Always on Availability groups Synchronous Configured between FPVI-SQL1HA and FPMC-SQL1HA (both got SQL Server 2014 installed)

No issues happened, and we had automatic failover happened without any data loss.

 

FPVI-SQL1HA(1595)-------100MB(N/W)--------FPMC-SQL1HA(Colocation)

As soon as we moved to Colocation to a 100MB network between two servers, issues started happening

 “An error was reported while committing a database transaction but it could not be determined whether the transaction succeeded or failed on the database server“

Ref:

https://msdn.microsoft.com/en-us/library/dn135335(v=sql.110).aspx

Synchronous mode :Transaction should be committed on Secondary FPMC and send ACK to primary FPVI for commit(Automatic Failover)

Asynchronous mode : Transaction  should be committed on Primary FPVI and  send ACK to Secondary FPMC(Manual Failover)

Adding table to replication publication witth out re intialilzing

 --First Check
 exec sp_helppublication 'ABC_PUB'
GO


--should be
--immediate sync=0
--allow_anonymus=0
--Run on your publisher database
EXEC sp_changepublication
@publication = 'ABC_PUB
@property = 'allow_anonymous' ,
@value = 'false'
GO
EXEC sp_changepublication
@publication = 'ABC_Pub',
@property = 'immediate_sync' ,
@value = 'false'
GO



 Step3:
exec sp_refreshsubscriptions 'ABC_Pub'
GO

Step4:
run SNAPSHOT agent at Replication Monitor --view snapshot agent and start
--http://www.sanssql.com/2011/01/sprefreshsubscriptions-useful.html
--go and see the count of new tables added to replication

--DO NOT TOUCH LOG READER AGENT OR DISTRIBUTOR AGENT --THEY run 24X7
Step4:
--Run on your publisher database
EXEC sp_changepublication
@publication = 'ABC_PUB',
@property = 'allow_anonymous' ,
@value = 'True'
GO
EXEC sp_changepublication
@publication = 'ABC_pub',
@property = 'immediate_sync' ,
@value = 'True'
GO


---on Subscriber  Check
USE ABC
go
SELECT Count(*) from table added--4434

Rename a table involved in replication from publication without re-initialization

EXEC  sys.sp_dropsubscription 

    @publication = N'ABC_pub',

    @article = N'Candidate',

    @subscriber = N'SQl4',

    @destination_db = N'ABC' ;

GO

EXEC sys.sp_droparticle 

    @publication = N'ABC_pub',

    @article = N'Candidate',

    @force_invalidate_snapshot = 1 ;

GO

 

 Use ABC

 go

 sp_rename 'Candidate','Candidate_X';

 --back to original 

 Use ABC

 go

 sp_rename 'Candidate_X','Candidate';

 

Dropping a table from publication without re-initialization

Dropping a table from publication without re-initialization

Dropping a table from the publication is interesting. If we think about it we would think that sp_droparticle would do for us but it is not the case. We have to drop the table subscription and then drop the article. Below are the steps

1. Drop the subscription for the table

 EXEC sp_dropsubscription 
'Publication name' , 'article to be dropped', 'subscriberame','subscriber database'

If there are more than one subscriber and you want to drop for all subscribers we can use ALL which is the default value also.

Because it is dropped only from the subscriber you will still see the table in publisher properties. It will appear till we execute the below command

EXEC sp_droparticle  @publication = 'Publication name',  @article = 'article to be dropped',
  @force_invalidate_snapshot = 1;
GO


That's it the article will be dropped from the publisher. It doesn't allow to drop the article till we drop the subscription of the article in all subscribers.

Dropping of article doesn't drop the object and if you want to drop the table altogether, then we have to use 'DROP TABLE' Command.

SQL Server Alert System: 'Severity 020'

Too many errors  with someone out side try to connect sql with windows login.....caused this severity..Fixed the login and re connected--issue is gone... 

Get replication Status from Multiple Publishers multiple subscribers and distributors

USE [Distribution]
GO

/****** Object:  Table [dbo].[ReplicationStatus]    Script Date: 6/24/2016 2:09:00 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ReplicationStatus](
[PublisherServername] [varchar](250) NULL,
[SubscriberServerName] [varchar](250) NULL,
[PublisherDB] [varchar](250) NULL,
[SubscriberDB] [varchar](250) NULL,
[DistributionStatus] [varchar](250) NULL,
[LogReaderStatus] [varchar](250) NULL,
[DateInserted] [datetime] NOT NULL DEFAULT (getdate())
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO






USE [Distribution]
GO

/****** Object:  StoredProcedure [dbo].[CheckReplicationStatus]    Script Date: 6/24/2016 1:45:42 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE Proc [dbo].[CheckReplicationStatus]
 as
BEGIN

---SQl1
BEGIN

INSERT INTO Distribution.dbo.ReplicationStatus
SELECT
'FPVI-SQL4' as Servername,
dl.subscriber_server,
dl.publisher_db,
dl.subscriber_db,
CASE dl.runstatus
WHEN 1 THEN 'Start'
WHEN 2 THEN 'Succeed'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retry'
WHEN 6 THEN 'Fail'
END AS DistributionRunStatus,
CASE rl.runstatus
WHEN 1 THEN 'Start'
WHEN 2 THEN 'Succeed'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retry'
WHEN 6 THEN 'Fail'
END AS LogReaderRunStatus,
GETDATE()
FROM (SELECT agent_id,
publisher_db,
Upper(ss.datasource) AS subscriber_server,
subscriber_db AS subscriber_db,
[name] AS distribution_agent,
DH.runstatus,
CASE
WHEN DH.runstatus = 1 THEN Datediff(SS, DH.TIME, Getdate())
WHEN DH.runstatus = 3 THEN DH.delivery_latency
ELSE 0
END AS distribution_latency
FROM (SELECT DHMAX.*
FROM distribution.dbo.MSdistribution_history DHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS TIME
FROM distribution.dbo.MSdistribution_history DH WITH(NOLOCK)
GROUP BY agent_id) MTDH
ON DHMAX.agent_id = MTDH.agent_id
AND DHMAX.TIME = MTDH.TIME) DH
JOIN distribution.dbo.MSdistribution_agents DA WITH(NOLOCK)
ON DH.agent_id = DA.id
JOIN MASTER.dbo.sysservers ss WITH(NOLOCK)
ON da.subscriber_id = ss.srvid) AS DL
LEFT OUTER JOIN (SELECT agent_id,
la.publisher_db,
lh.runstatus,
CASE
WHEN lh.runstatus = 1 THEN Datediff(SS, LH.TIME, Getdate())
WHEN lh.runstatus = 3 THEN lh.delivery_latency
ELSE 0
END AS reader_latency
FROM (SELECT LHMAX.*
FROM distribution.dbo.MSlogreader_history LHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS [time]
FROM distribution.dbo.MSlogreader_history WITH(NOLOCK)
GROUP BY agent_id) MTLH
ON LHMAX.agent_id = MTLH.agent_id
AND LHMAX.TIME = MTLH.TIME) LH
JOIN distribution.dbo.mslogreader_agents LA WITH(NOLOCK)
ON lH.agent_id = lA.id) AS RL
ON DL.publisher_db = RL.publisher_db
ORDER BY dl.publisher_db,
dl.distribution_agent
END





 BEGIN
---sql1
INSERT INTO Distribution.dbo.ReplicationStatus
SELECT
'FPSQL1' as Servername,
dl.subscriber_server,
 dl.publisher_db,
dl.subscriber_db,
 CASE dl.runstatus
WHEN 1 THEN 'Start'
WHEN 2 THEN 'Succeed'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retry'
WHEN 6 THEN 'Fail'
END AS DistributionRunStatus,
 CASE rl.runstatus
WHEN 1 THEN 'Start'
WHEN 2 THEN 'Succeed'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retry'
WHEN 6 THEN 'Fail'
END AS LogReaderRunStatus,
GETDATE()
FROM (SELECT agent_id,
publisher_db,
Upper(ss.datasource) AS subscriber_server,
subscriber_db AS subscriber_db,
[name] AS distribution_agent,
DH.runstatus,
CASE
WHEN DH.runstatus = 1 THEN Datediff(SS, DH.TIME, Getdate())
WHEN DH.runstatus = 3 THEN DH.delivery_latency
ELSE 0
END AS distribution_latency
FROM (SELECT DHMAX.*
FROM [FPMC-DIST1].distribution.dbo.MSdistribution_history DHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS TIME
FROM [FPMC-DIST1].distribution.dbo.MSdistribution_history DH WITH(NOLOCK)
GROUP BY agent_id) MTDH
ON DHMAX.agent_id = MTDH.agent_id
AND DHMAX.TIME = MTDH.TIME) DH
JOIN [FPMC-DIST1].distribution.dbo.MSdistribution_agents DA WITH(NOLOCK)
ON DH.agent_id = DA.id
JOIN [FPMC-DIST1]. MASTER.dbo.sysservers ss WITH(NOLOCK)
ON da.subscriber_id = ss.srvid) AS DL
LEFT OUTER JOIN (SELECT agent_id,
la.publisher_db,
lh.runstatus,
CASE
WHEN lh.runstatus = 1 THEN Datediff(SS, LH.TIME, Getdate())
WHEN lh.runstatus = 3 THEN lh.delivery_latency
ELSE 0
END AS reader_latency
FROM (SELECT LHMAX.*
FROM [FPMC-DIST1].distribution.dbo.MSlogreader_history LHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS [time]
FROM [FPMC-DIST1].distribution.dbo.MSlogreader_history WITH(NOLOCK)
GROUP BY agent_id) MTLH
ON LHMAX.agent_id = MTLH.agent_id
AND LHMAX.TIME = MTLH.TIME) LH
JOIN [FPMC-DIST1]. distribution.dbo.mslogreader_agents LA WITH(NOLOCK)
ON lH.agent_id = lA.id) AS RL
ON DL.publisher_db = RL.publisher_db
ORDER BY dl.publisher_db,
dl.distribution_agent
END


BEGIN
----fpvi-sql5
INSERT INTO Distribution.dbo.ReplicationStatus
SELECT
'FPVI-SQL5' as Servername,
dl.subscriber_server,
 dl.publisher_db,
dl.subscriber_db,
 CASE dl.runstatus
WHEN 1 THEN 'Start'
WHEN 2 THEN 'Succeed'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retry'
WHEN 6 THEN 'Fail'
END AS DistributionRunStatus,
 CASE rl.runstatus
WHEN 1 THEN 'Start'
WHEN 2 THEN 'Succeed'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retry'
WHEN 6 THEN 'Fail'
END AS LogReaderRunStatus
,GETDATE()
FROM (SELECT agent_id,
publisher_db,
Upper(ss.datasource) AS subscriber_server,
subscriber_db AS subscriber_db,
[name] AS distribution_agent,
DH.runstatus,
CASE
WHEN DH.runstatus = 1 THEN Datediff(SS, DH.TIME, Getdate())
WHEN DH.runstatus = 3 THEN DH.delivery_latency
ELSE 0
END AS distribution_latency
FROM (SELECT DHMAX.*
FROM [fpvi-sql5].distribution.dbo.MSdistribution_history DHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS TIME
FROM [fpvi-sql5].distribution.dbo.MSdistribution_history DH WITH(NOLOCK)
GROUP BY agent_id) MTDH
ON DHMAX.agent_id = MTDH.agent_id
AND DHMAX.TIME = MTDH.TIME) DH
JOIN [fpvi-sql5].distribution.dbo.MSdistribution_agents DA WITH(NOLOCK)
ON DH.agent_id = DA.id
JOIN [fpvi-sql5]. MASTER.dbo.sysservers ss WITH(NOLOCK)
ON da.subscriber_id = ss.srvid) AS DL
LEFT OUTER JOIN (SELECT agent_id,
la.publisher_db,
lh.runstatus,
CASE
WHEN lh.runstatus = 1 THEN Datediff(SS, LH.TIME, Getdate())
WHEN lh.runstatus = 3 THEN lh.delivery_latency
ELSE 0
END AS reader_latency
FROM (SELECT LHMAX.*
FROM [fpvi-sql5].distribution.dbo.MSlogreader_history LHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS [time]
FROM [fpvi-sql5].distribution.dbo.MSlogreader_history WITH(NOLOCK)
GROUP BY agent_id) MTLH
ON LHMAX.agent_id = MTLH.agent_id
AND LHMAX.TIME = MTLH.TIME) LH
JOIN [fpvi-sql5]. distribution.dbo.mslogreader_agents LA WITH(NOLOCK)
ON lH.agent_id = lA.id) AS RL
ON DL.publisher_db = RL.publisher_db
ORDER BY dl.publisher_db,
dl.distribution_agent
END

BEGIN
---fpvisql6
INSERT INTO Distribution.dbo.ReplicationStatus
SELECT
'FPVI-SQL6' as Servername,
dl.subscriber_server,
 dl.publisher_db,
dl.subscriber_db,
 CASE dl.runstatus
WHEN 1 THEN 'Start'
WHEN 2 THEN 'Succeed'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retry'
WHEN 6 THEN 'Fail'
END AS DistributionRunStatus,
 CASE rl.runstatus
WHEN 1 THEN 'Start'
WHEN 2 THEN 'Succeed'
WHEN 3 THEN 'In progress'
WHEN 4 THEN 'Idle'
WHEN 5 THEN 'Retry'
WHEN 6 THEN 'Fail'
END AS LogReaderRunStatus
,GETDATE()
FROM (SELECT agent_id,
publisher_db,
Upper(ss.datasource) AS subscriber_server,
subscriber_db AS subscriber_db,
[name] AS distribution_agent,
DH.runstatus,
CASE
WHEN DH.runstatus = 1 THEN Datediff(SS, DH.TIME, Getdate())
WHEN DH.runstatus = 3 THEN DH.delivery_latency
ELSE 0
END AS distribution_latency
FROM (SELECT DHMAX.*
FROM [fpvi-sql6].distribution.dbo.MSdistribution_history DHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS TIME
FROM [fpvi-sql6].distribution.dbo.MSdistribution_history DH WITH(NOLOCK)
GROUP BY agent_id) MTDH
ON DHMAX.agent_id = MTDH.agent_id
AND DHMAX.TIME = MTDH.TIME) DH
JOIN [fpvi-sql6].distribution.dbo.MSdistribution_agents DA WITH(NOLOCK)
ON DH.agent_id = DA.id
JOIN [fpvi-sql6]. MASTER.dbo.sysservers ss WITH(NOLOCK)
ON da.subscriber_id = ss.srvid) AS DL
LEFT OUTER JOIN (SELECT agent_id,
la.publisher_db,
lh.runstatus,
CASE
WHEN lh.runstatus = 1 THEN Datediff(SS, LH.TIME, Getdate())
WHEN lh.runstatus = 3 THEN lh.delivery_latency
ELSE 0
END AS reader_latency
FROM (SELECT LHMAX.*
FROM [fpvi-sql6].distribution.dbo.MSlogreader_history LHMAX WITH(NOLOCK)
INNER JOIN (SELECT agent_id,
MAX([time]) AS [time]
FROM [fpvi-sql6].distribution.dbo.MSlogreader_history WITH(NOLOCK)
GROUP BY agent_id) MTLH
ON LHMAX.agent_id = MTLH.agent_id
AND LHMAX.TIME = MTLH.TIME) LH
JOIN [fpvi-sql6]. distribution.dbo.mslogreader_agents LA WITH(NOLOCK)
ON lH.agent_id = lA.id) AS RL
ON DL.publisher_db = RL.publisher_db
ORDER BY dl.publisher_db,
dl.distribution_agent
END

END




GO

Delete on huge table could break replication

I deleted 202451046 rows(14GB) in a table, which is part of replication, and this caused LDF(log) file to grow from 5 gb to 70gb.

then, log reader was taking for ever to move the transactions from log to distributor 
and  distributor was waiting ..bottom line Replication is OUT of SYNC.

so, please delete by few numbers, instead of deleting entire big table.

Thanks,