• Hi all,

    Nice article.

    I'm working in a similar trigger but I'm recording information in a table in a Linked server for consolidation issues, because I have several instances and several server and I need to collect all information in only a database, but when I try to the insert command, I only get a set of errors. I've checked previously the trigger in the local server and runs ok, but the fails appear when I try to execute from a remote server.

    My insert command:

    INSERT INTO [SRSQL1001\MANAGEMENT].[DBA_AUDIT].[dbo].[ENV_SRV_INS_DB_CHG]

    ([ENV_SRV_INS_DB_CHG_Server]

    ,[ENV_SRV_INS_DB_CHG_Name]

    ,[ENV_SRV_INS_DB_CHG_EventType]

    ,[ENV_SRV_INS_DB_CHG_PostTime]

    ,[ENV_SRV_INS_DB_CHG_Login]

    ,[ENV_SRV_INS_DB_CHG_TSQLCommand]

    ,[ENV_SRV_INS_DB_CHG_Completed])

    values(@ServerName,@DBName,@EventType,@PostTime,@login,@TSQLCommand,@comp)

    errors set

    Msg 3984, Level 16, State 1, Line 1

    Cannot acquire a database lock during a transaction change.

    Msg 3985, Level 16, State 2, Line 1

    An error occurred during the changing of transaction context. This is usually caused by low memory in the system. Try to free up more memory.

    Msg 3984, Level 16, State 1, Line 1

    Cannot acquire a database lock during a transaction change.

    Msg 3985, Level 16, State 1, Line 1

    An error occurred during the changing of transaction context. This is usually caused by low memory in the system. Try to free up more memory.

    I have checked if I delete insert command database is created without errors.

    Thanks in advance for your help

    F.Racionero

    Francisco Racionero
    twitter: @fracionero