DDL Triggers Distributed Transaction Error

[pgraves]

Hi all,

I have just started to try and implement DDL auditing triggers in SQL Server 2005 and get a Distributed Transaction error when inserting into a remote audit table. Part of the error reads:
'Cannot acquire a database lock during a transaction change. An error occurred during the changing of transaction context. This is usually caused by low memory in the system'

I have created a linked server between the two servers which works correctly and have assigned the correct permissions. There are no memory problems when the insert is attempted.

I have even tried a basic test trigger which also fails with the same error:
CREATE TRIGGER [tr_AuditTest]
ON ALL SERVER
FOR ALTER_AUTHORIZATION_SERVER,
CREATE_DATABASE,ALTER_DATABASE,DROP_DATABASE,
CREATE_ENDPOINT,DROP_ENDPOINT,
CREATE_LOGIN,ALTER_LOGIN,DROP_LOGIN,
GRANT_SERVER,DENY_SERVER,REVOKE_SERVER
AS
BEGIN
insert into [AUDITSERVER].[Audit].[dbo].test values(2)
END

The insert only fails when the trigger is fired. I am able to insert into the remote table using a T-SQL INSERT.

If anyone has successfully implemented auditing using DDL triggers and using a remote audit database, I would apreciate any help you can give.

Thanks,
Paul

 

[ptheriault]

Have you tried to test the trigger with just one event? Recreate your trigger for Database create then test it.
Also, Is that your insert statement? Why aren't you using EVENTDATA() to insert the DDL from the event?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[pgraves]

I just tried with one event (CREATE DATABASE). Same error.

The insert statement is a test. As I mentioned, the trigger code in my post is just a test trigger. I can't event do a simple insert to a remote table when the DDL trigger is fired.

I have tried with Distributed Transactions and XACT_ABORT settings and get the same error every time.

 

[ptheriault]

I would think that should work. Although I have mine set up to write to an audit database on the same server.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[pgraves]

Ok, this problem is now solved. I should have used AFTER instead of FOR in the create trigger statement.

 

[ptheriault]

Thanks for posting how you corrected it.

- Paul

- If at first you don't succeed, find out if the loser gets anything.