Timeout error

Question

Post reply

Timeout error

Satya_0000

Mr or Mrs. 500

Points: 544
More actions
July 23, 2012 at 8:39 pm

#259692

jobs are failing with below error message.
DB ErrorTimeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
When I checked SQL server error log we found below error at same time. also maintenance jobs (update stats)are running at same time which we are considering as primary cause.
SQL error log message:
Error: 18056, Severity: 20, State: 1.
The client was unable to reuse a session with SPID 80, which had been reset for connection pooling. The failure ID is 1. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
Message
Error: 17892, Severity: 20, State: 1.
Logon failed for login 'abc\user' due to trigger execution.
Any advice will be highly appreciated!!!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Satheesh E.P. SSCrazy Points: 2509 More actions · Answer 1

Have you deployed any Logon triggers in the server?

---------------------------------------------------
Thanks,
Satheesh.

rraja Default port Points: 1486 More actions · Answer 2

rraja

Default port

Points: 1486

July 24, 2012 at 9:15 am

#1516883

Yes We have logon trigger.

Daxesh Patel Hall of Fame Points: 3208 More actions · Answer 3

Satya_0000 (7/23/2012)
jobs are failing with below error message.
DB ErrorTimeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
When I checked SQL server error log we found below error at same time. also maintenance jobs (update stats)are running at same time which we are considering as primary cause.
SQL error log message:
Error: 18056, Severity: 20, State: 1.
The client was unable to reuse a session with SPID 80, which had been reset for connection pooling. The failure ID is 1. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
Message
Error: 17892, Severity: 20, State: 1.
Logon failed for login 'abc\user' due to trigger execution.
Any advice will be highly appreciated!!!

Looks like the login account does not have enough permissions (probably "inserts into some table" in trigger).

If that is the case, you can modify trigger and add WITH EXECUTE AS 'sa'

e.g.

ALTER TRIGGER [TriggerName]

ON ALL SERVER WITH EXECUTE AS 'sa'

FOR LOGON

AS

Here 'sa' is just an example, you can use suitable acount

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

What does the login trigger do?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Daxesh Patel (7/24/2012)
Looks like the login account does not have enough permissions (probably "inserts into some table" in trigger).

Where do you see a permissions related error?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Daxesh Patel Hall of Fame Points: 3208 More actions · Answer 6

GilaMonster (7/24/2012)
Daxesh Patel (7/24/2012)
Looks like the login account does not have enough permissions (probably "inserts into some table" in trigger).
Where do you see a permissions related error?

You are right, this is not permission related (my apologies).

This can be a ROLLBACK in logon for some reason