June 6, 2007 at 8:39 am
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
June 6, 2007 at 10:13 am
I have now solved this problem. I used FOR instead of AFTER in the CREATE TRIGGER statement.
Inserts working correctly now.
June 7, 2007 at 1:24 am
thanks for the feedback.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply