The client was unable to reuse a session error

• Michael L John

  One Orange Chip

  Points: 27038

  More actions

  July 22, 2014 at 9:13 am

  #296729

  While trying to capture some info to be used to troubleshoot various problems, we created a trigger on the server that captures the logins and writes to a table.

  The trigger:

  CREATE TRIGGER [Trigger_LoginCapture]

  ON ALL SERVER /*WITH EXECUTE AS 'sa'*/ FOR LOGON

  AS

  BEGIN

  DECLARE @data XML

  SET @data = EVENTDATA()

  INSERT INTO DBAutilities.dbo.Server_Logon_History

  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

  The "execute as sa" was run first. This was then commented out, and insert was granted to public on the table.

  In either case, as soon as the trigger is enabled, we start receiving this message in the logs:

  Message

  The client was unable to reuse a session with SPID 198, which had been reset for connection pooling. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

  If the trigger is disabled, the error goes away.

  Any clue? I have seen this error before, but not in this context!!!

  Michael L John
  If you assassinate a DBA, would you pull a trigger?
  To properly post on a forum:
  http://www.sqlservercentral.com/articles/61537/