Logon triggers

Question

Logon triggers

db_bunny

Right there with Babe

Points: 763
More actions
May 28, 2008 at 9:43 pm

#182914

Hello fellow SQL buffs,
Just a quick and probably silly question, but I am writing a logon trigger which executes a stored proc each time a user logs onto the server. The stored proc then inserts connection info about the session to a table.
My code is as follows:
CREATE TRIGGER login_trigger ON ALL SERVER
FOR LOGON
AS
commit
BEGIN
declare @SessID varchar (5)
set @SessID = @@spid
exec BigBro.dbo.LogUserActivity @SessID
END
For some reason it seems to log multiple entries for the same SPID multiple times for the one connection, rather than just the one time when the connection is established. Does SQL server constantly reinitialise it's connection once a user is logged in? I wouldn't have thought the logon trigger should be triggered more than once for a particular session.
If anyone can explain why SQL behaves this way, it would be much appreciated.
Thank you.

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

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Well 1), yes, SQL Server does reuse SPIDs/Sessions within a Connection by reinitializing them (this is probably what you are seeing),

and 2) you can multiple sessions with the same SPID at the same time on the same connection(they show up with different "ECID"s under sp_Who). This appears to be some kind of high-level threading adn I do not know if each ECID goes through the Login triggers or not.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

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

Could you show us what LogUserActivity does?

Does the the app that uses this DB use Connection Pooling?

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

db_bunny Right there with Babe Points: 763 More actions · Answer 3

Thanks Rbarry, I think you may be right. Intersting that it works this way.

The stored proc just grabs a whole bunch of connection information based on the SPID passed via the trigger and inserts it into a table. Eg:

select @hostname = host_name from sys.dm_exec_connections

insert into loggingtable @hostname

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

There is a sid and a login time in sysprocesses. The login time stays constant when a connection is reinitialized without being disconnected, and I think the sid stays constant as well. You could store one or both of these in your table and check for duplicates before re-inserting.

select sid, login_time from master.sys.sysprocesses where spid = @@spid

harish_ravi Say Hey Kid Points: 662 More actions · Answer 5

Hello boss,

though i couldn't solve your doubt

I have a suggestion.

if you want to collect statistics like which user logged at what time

You can do like this ...

create a table with 2 columns SPID and timestamp. make SPID column as unique. so you cannot insert duplicate rows.