Need Help to Find Problem Trigger 2

[fuzzyocelot]

Hello!

We have a stand-alone server running SQL Server 2005 SP2 for a specific application. It contains a database that has 90+ tables. Of these tables, about 30 have insert, update, and delete triggers. They are After triggers and are being used for audit purposes. Meaning, whenever someone does an insert, update, or delete on a specific table, certain information is written to an audit log table.

During testing, someone noticed there was a trigger in there somewhere that inserts thousands of records into the audit log table. However, no one was able to determine which trigger caused the issue. Traces were set but the culprit wasn't found. Prior to going live, the audit log table was cleared out. This problem trigger hasn't fired off yet but the developers are nervous this could happen again and cause major issues.

I'm not sure what to set up on the SQL server side that would help me find this problematic trigger. I would rather not wait until it happens again but I don't have time to go through every trigger. I thought about setting a trace but on what object(s)? Does anyone have any ideas on how I can find this trigger?

Thanks!

 

[bborissov]

Didn't you added the table name in your audit log table?
Maybe some trigger fires recursively. If you have triggers ONLY for audit log, maybe it is better to forbid recursive triggers?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[RiverGuy]

Also, are you sure it's not in the application? If it's been coded inefficiently and performs updates based on events when nothing has changed, you can definitely fill up your audit log.

 

[fuzzyocelot]

Thanks to both of you for responding.

bborissov, good point. I wasn't the one who created the tables or triggers. However, the table name is in the audit log table. So that should help. I don't know if any of the triggers are recursive. I'll try to check for that. As far as I know, none of them should be recursive.

RiverGuy, you also have a good point. That thought occurred to me as well. It could very well be in the application. I believe a good chunk of the code is in stored procedures within the database itself, though. There are 100+ stored procedures. If the problem is in one of them, it won't be easy finding it. They're all pretty large and complicated, of course.

I'm thinking that we'll probably end up watching the size of the audit log table so we'll at least know when it happens again. Hopefully it'll tell us which table so we can search the stored procedures and check the triggers on that table.

Thanks!

 

[fuzzyocelot]

By the way, I just checked and the "Recursive Triggers" option is disabled on the database. So it's probably not a recursive trigger. If it is a trigger, maybe it's a nested trigger.

Thanks!

 

[bborissov]

You could try:

USE [master]
GO
ALTER DATABASE [YourDatabase] SET RECURSIVE_TRIGGERS OFF WITH NO_WAIT
GO
ALTER DATABASE [YourDatabase] SET RECURSIVE_TRIGGERS OFF 
GO

BUT BE VERY CAREFUL WITH THIS!!!!!
That could break whole logic of you Database!!!
Maybe for testing purposes you could do this just to see IF these records will be there again.
Other choice is to change audit log table and add the table name which is changed. But that requires to reprogram all triggers.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[calico]

Hi

I will try to check for you on what object(s) you can set a profiler trace. I don"t know if it possible.

Calico

 

[fuzzyocelot]

Thanks, bborissov. The recursive trigger option is already off on the database. Also, the name of the table that is affected is already part of the triggers and a field in the audit log table.

Thanks, calico, as well. I thought about doing a profiler trace but it would have to run somewhere constantly and indefinitely. Plus I don't know what kind of overhead that would cause. I'll have to keep looking into that as an option as well.