Server Logon Trigger fails after deleting many records in "Logon-Table" -SQL Error 2801

Question

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?

Trigger definition

 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)[1]', 'varchar(500)'),
 @@SPID,
 program_name(),
 HOST_NAME(),
 EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(500)')
 END TRY
 BEGIN CATCH
 PRINT ERROR_MESSAGE()
 END CATCH
 END
 GO
 SET ANSI_NULLS OFF
 GO
 SET QUOTED_IDENTIFIER OFF
 GO

• Kin Shah: As perr your edit - The definition of object ‘my_proc’ has changed since it was compiled. can you recompile the trigger using sp_recompile ?
• Marcello Miorelli: or you better use sp_refreshsqlmodule

Answer 1

From a logging perspective, if you intend to delete more than 50% of the rows from a table, you should use the following methodology:

• Set the transaction isolation level to serializable
• Begin a transaction
• Create a new table, named the same as the original table, with a _x suffix
• Copy the results you want to keep from the original table to the _x one
• Drop the original table
• Rename the _x table back to the original table
• Commit the transaction

Also, the PK_ID column that you described should be the PK and clustred index for the table, otherwise its just a heap.

Also, in terms of logging server logins, perhaps you'd have better luck using an Audit or extended event session?

The "The definition of object ‘my_proc’ has changed since it was compiled" error that you described should be resolvable by calling sp_recompile.

• mustaccio: While all of this may be true in some circumstances, how does it answer the question?
• Matthew Sontum: I'm saying that since you are failing with your current approach that you should try a new one that will work. Then you won't need to care why the current approach failed. Some mysteries aren't worth solving, it's better to seek an alternate solution.