We have a server logon trigger defined that logs every login with information like windows/sql user name, client and time in a table.
The table doesn't have clustered index, but it does have a "PK_ID" column, which is bigint and autoincrement.
Last week, we deleted roughly 80 million from a total of 90 million records in that "login table" (with a
delete from..., we didn't
truncate the table).
But since then we see multiple cases where the logon trigger fails with
Logon failed for login 'SQL\xy' due to trigger execution. Changed database context to ‘master’. (Microsoft SQL Server, Error: 17892)
We did restart the whole windows server a couple of times and afterwards the error went away for a few hours, but seems to come back every now and then. Note that this only started to appear after we deleted so many records.
We found out that prior to users not being able to login, we get the following error:
Msg 2801, Level 16, State 1,: The definition of object 'my_proc' has changed since it was compiled.
Afterwards we get the error 17892 as described above.
Does anybody know what the problem could be?
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [Tr_My_Logon] ON ALL SERVER WITH EXECUTE AS 'xy' FOR LOGON AS BEGIN BEGIN TRY INSERT INTO mydatabase.myschema.myLogonHistoryTable SELECT GETDATE(), ORIGINAL_LOGIN(), EVENTDATA().value('(/EVENT_INSTANCE/LoginType)', 'varchar(500)'), @@SPID, program_name(), HOST_NAME(), EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)', 'varchar(500)') END TRY BEGIN CATCH PRINT ERROR_MESSAGE() END CATCH END GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO