Sql-server – Server Logon Trigger fails after deleting many records in “Logon-Table” -SQL Error 2801

sql serversql-server-2008trigger

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

Best Answer

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.

Related Question