March 14, 2016 at 9:47 pm
Hi everyone!
I had an issue crop up this afternoon on a dev server that I have never seen before and has me completely stumped. I hope someone can point me in the right direction.
The server itself is running Microsoft SQL Server Standard Edition (64-bit) version 10.50.2550.0 on Microsoft Windows NT 6.1 (7601), but the database I was working on is set to compatibility level 80 due to vendor software limitations.
The database itself is part of an off the shelf platform, so we have limited control over how various business processes are implemented. I was developing an INSTEAD OF INSERT trigger to meet a new requirement from the users and started getting this error:
[font="Courier New"]
Cannot set XACT ABORT to OFF inside the trigger execution unless the database compatibility is 90.
Msg 3616, Level 16, State 2, Procedure DummySproc, Line 3
An error was raised during trigger execution. The batch has been aborted and the user transaction, if any, has been rolled back.[/font]
I should add that I couldn't find any SET XACT_ABORT statements in any procedure, trigger, function, etc anywhere.
This happens when I try to DROP, ALTER or CREATE any kind of database object - tables, triggers, procedures, views, etc, but not just in the database I was working on, but across the entire server. Interestingly enough, CREATE DATABASE gives me this:
[font="Courier New"]Msg 1807, Level 16, State 3, Line 1
Could not obtain exclusive lock on database 'model'. Retry the operation later.
Msg 1802, Level 16, State 4, Line 1
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
[/font]
Finally, I have tried setting the compatibility level on the database to 90, and the same "Cannot set XACT ABORT to OFF" occurs, but the compatibility level is successfully changed.
I have no idea what is causing DDL statements to fail server wide - I didn't even think that was possible. Any suggestions would be greatly appreciated, as I am not looking forward to explaining that the development server is completely FUBARed and that I have no idea why 🙂
PS Dropping every database (if we could drop databases at all) is certainly an option, but reinstalling SQL Server likely is not.
Thanks,
DJ
March 15, 2016 at 2:51 am
Someone's implemented a DDL trigger somewhere, and it's broken.
Check in Object Explorer. Each DB, programability, triggers or Server Objects-> triggers for server-wide triggers (which is probably more likely where this one is)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 15, 2016 at 5:06 am
Bingo! Found a trigger in Server Objects that uses SET XACT_ABORT OFF
Ironically enough, it was called RG_SQLLighthouse (or something like that, it's been banished to the realm of wind and ghosts), probably left over from a trial version of RedGate DLM Dashboard that somehow managed not to cause any issues until now.
September 9, 2016 at 2:32 pm
Thanks so much for posting this issue and solution. I was going nuts trying to figure out what was going on. I found and disabled the trigger: System Objects\Triggers\RG_SQLLighthouse_DDLTrigger and all my DDL statements now execute without the error msg: "Cannot set XACT ABORT to OFF...." Looks to have been added when I installed RedGate SQL Search tool.
Thanks again
Pete
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply