September 6, 2011 at 2:38 pm
Hi,
We ran into a weird issue with logon triggers, and I'm hoping someone else might have some insight. Basically, we have a couple of temp tables we use to store "session-specific" data, and we created a logon trigger that clears out any existing data in those temp tables keyed to the new session's SPID. Well, it worked great in our dev environment, but when we deployed to our uat environment, strange things started happening. After much wailing and gnashing of teeth, what we finally discovered was that existing sessions were being passed around between different users' connections, which would be fine, except that when the existing session was re-associated to a new connection, the logon trigger wasn't being fired, hence the data in the temp tables wasn't cleared out the way we needed it to be, and thus the strange issues we were having.
Has anyone else seen behavior like this? Is it a bug or a feature?
Any insight would be much appreciated.
Thanks!
-Chris
September 6, 2011 at 2:55 pm
Connections are expensive to create in sql server and this is why sql server uses Connection Pooling by default.
Connections that are not in use are put into a pool and reused when necessary instead of destroying and recreating them (very simplified explination, see here for more info.
Regardless, a login, be it creating a new connection or using one already in existance in the pool, will still be seen as a login event.
Are you doing anything out of the ordinary in the trigger? can you post code/pseudo code/scrubbed code?
September 6, 2011 at 3:55 pm
winston Smith (9/6/2011)
Connections are expensive to create in sql server and this is why sql server uses Connection Pooling by default.Connections that are not in use are put into a pool and reused when necessary instead of destroying and recreating them (very simplified explination, see here for more info.
Regardless, a login, be it creating a new connection or using one already in existance in the pool, will still be seen as a login event.
Are you doing anything out of the ordinary in the trigger? can you post code/pseudo code/scrubbed code?
Well, SQL Server doesn't use connection pooling, the providers that connect to it do; i.e. it's something that happens completely outside of SQL Server itself. What I'm seeing might better be referred to as "session pooling". That is, it looks like SQL Server throws sessions in a pool when the connections they're associated with are idle, then randomly pulls one out of the pool and re-assigns it to a connection when that connection wants to execute a command again. Now that I think about it, I honestly can't say whether every connection, when it's initially created, is firing the logon trigger or not, though I'd guess that it is. It's the fact that sessions seem to be being shared between connections that's giving me fits.
In any event, this "session pooling" idea is purely conjecture on my part, but seems to fit with what we're seeing. I'm wondering if anyone else can shed some light on what might be going on, and if there are any settings I might use to control it.
To answer your question, no we're not doing anything out of the ordinary in the logon trigger. It checks to see if the database where the tables live is up, then checks to see if the tables exist, and if they do, it deletes all rows where the "SPID" column equals @@SPID. Something like this:
CREATE TRIGGER trg_OurApp_Logon
ON ALL SERVER
WITH EXECUTE AS 'appowner'
FOR LOGON
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT 1 FROM master.sys.databases WHERE name = 'OurDB' AND state_desc = 'ONLINE')
BEGIN
IF OBJECT_ID('OurDB.common.SessionVariable') IS NOT NULL
DELETE FROM OurDB.common.SessionVariable WHERE SPID = @@SPID;
IF OBJECT_ID('OurDB.common.SearchResult') IS NOT NULL
DELETE FROM OurDB.common.SearchResult WHERE SPID = @@SPID;
END;
END;
GO
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply