5

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
1
  • 1
    As perr your edit - The definition of object ‘my_proc’ has changed since it was compiled. can you recompile the trigger using sp_recompile ? – Kin Shah Oct 3 '15 at 2:27
-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.

2
  • While all of this may be true in some circumstances, how does it answer the question? – mustaccio Feb 11 '17 at 22:33
  • 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. – Matthew Sontum Feb 11 '17 at 22:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.