Logon failed for login 'ABC' due to trigger execution.

All of a sudden developers complained about issue.

System.Data.Entity.Core.EntityException: The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: Logon failed for login 'ABC' due to trigger execution.

Changed database context to 'XYZ'.

Changed language setting to us_english.

Solution:

I DISABLED TRIGGER. 

Help:

http://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context-to-master/

I used below trigger to test and find out login information .

USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [Trigger_ServerLogon]

ON ALL SERVER FOR LOGON

AS

BEGIN

DECLARE @data XML

SET @data = EVENTDATA()

INSERT INTO dbo.ServerLogonHistory

SELECT @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(512)')

, @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')

, @data.value('(/EVENT_INSTANCE/SPID)[1]', 'nvarchar(4)')

, @data.value('(/EVENT_INSTANCE/ServerName)[1]', 'nvarchar(512)')

, @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(512)')

, @data.value('(/EVENT_INSTANCE/LoginType)[1]', 'nvarchar(512)')

, @data.value('(/EVENT_INSTANCE/SID)[1]', 'nvarchar(512)')

, @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'nvarchar(512)')

, @data.value('(/EVENT_INSTANCE/IsPooled)[1]', 'nvarchar(1)')

END

GO

DISABLE TRIGGER [Trigger_ServerLogon] ON ALL SERVER

GO

Hi, I need a small tip from experts,

I have a table like below with

ABC( id int,datecreated datetime, CC Varchar(20),Accountnumber(20), name varchar(10), OldRecord XML, Newrecord XML)

I have some thousands of records in ABC table, 

I have data something like below

Select * from ABC

 id datecreated        CC      Accountnumber        name                   OldRecord                                                            Newrecord

1   2016-07-11     null       #4%&*@))@1       NICK                 <row cc='1234' name='nick'  date='2016-07-11'>           null 

now, i would like to search data from Oldrecord column or new record column which are XML data type columns 

like below:

SELECT * from ABC

where Oldrecord like '%Nick%' 

or 

SELECT * from ABC

where Oldrecord like '%CC=1234%' 

i tried running the queries above and i got below error:

Argument data type xml is invalid for argument 1 of like function.

Please help me on how to search data in XML column data type 

Answer:

SELECT Top 10 * from  [dbo].[AuditLog] with(nolock)

where  cast(NewRecord as nvarchar(max)) like '%cc=1234%'

“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,

Add Articles to existing publication(transactional replication) with out generating whole snapshot

--========================================================================
-- GET A LIST OF SUBSCRIBERS
 select
db_name() PublisherDB
, sp.name as PublisherName
, sa.name as TableName
, UPPER(srv.srvname) as SubscriberServerName
from dbo.syspublications sp
join dbo.sysarticles sa on sp.pubid = sa.pubid
join dbo.syssubscriptions s on sa.artid = s.artid
join master.dbo.sysservers srv on s.srvid = srv.srvid

exec sp_helppublication 'DB_pub'
GO
--Check if allow_anonymous=0,immediate_sync=0 or or not if not zero run below command

--Before running the sp_refreshsubscriptions SP, make sure that the publisher properties "allow_anonymous" and "immediate_sync" are set to "False",
-- if these 2 options are set to "True" then this SP will mark all the articles for
--  generating snapshot instead of marking only the newly added articles.


EXEC sp_changepublication
@publication = 'DBpub',
@property = N'allow_anonymous',
@value = 'false'
GO
EXEC sp_changepublication
@publication = 'DB_pub',
@property = N'immediate_sync',
@value = 'false'
GO


--Then add artciles

EXEC sp_addarticle @publication = N'DB_pub',
                   @article = N'LeadSourceGroup',
                   @source_object=N'LeadSourceGroup'



--========================================================================
-- Only add the subscribers if they don't exist
--========================================================================
--EXEC sp_addsubscription
--  @publication = N'mydatabase',
--   @article = N'OpenInvoiceAddress',
--  @subscriber = N'my_subscriber_01',
--  @destination_db = N'mydatabase'
--GO

-- EXEC sp_addsubscription
--  @publication = N'mydatabase',
--   @article = N'OpenInvoiceAddress',
--  @subscriber = N'SQLmy_subscriber_02',
--  @destination_db = N'mydatabase'
--GO

--Should on poublisher
EXEC sp_refreshsubscriptions @publication = N'db_pub'
GO
-- Start the Snapshot Agent job.This will only generetae snapshot for
--add 1 table or any tables adeed
EXEC sp_startpublication_snapshot @publication = N'DB_pub'
go

----MUST
--enable back
EXEC sp_changepublication
@publication = 'DB_pub',
@property = N'allow_anonymous',
@value = 'True'
GO
EXEC sp_changepublication
@publication = N'DB_pub,
@property = N'immediate_sync',
@value = 'true'
GO